vendredi 28 août 2015

Unspecified FailedOperationException in Microsoft.SqlServer.Management.Smo.Server

I'm working on some SQL Server administration scripts. Part of the script needs to add/remove SQL Logins. The following works, adding john_doe0 into the root Security.Logins:

Import-Module "sqlps" -DisableNameChecking
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | Out-Null

$SQL_SERVER = New-Object 'Microsoft.SqlServer.Management.Smo.Server' $env:ComputerName
$username = "john_doe0"
$password = "ARandomPasswordString123"

$login = New-Object 'Microsoft.SqlServer.Management.Smo.Login' -ArgumentList $SQL_SERVER, $username
$login.LoginType = 'SqlLogin'

if (!($SQL_SERVER.logins).Contains($username)) {
    $login.Create($password);
}

But this doesn't, causing a drop failed error and an unspecified FailedOperationException.

Import-Module "sqlps" -DisableNameChecking
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | Out-Null

$SQL_SERVER = New-Object 'Microsoft.SqlServer.Management.Smo.Server' $env:ComputerName
$username = "john_doe0"
$password = "ARandomPasswordString123"

$login = New-Object 'Microsoft.SqlServer.Management.Smo.Login' -ArgumentList $SQL_SERVER, $username
$login.LoginType = 'SqlLogin'

if (($SQL_SERVER.logins).Contains($username)) {
    $login.Drop();
}

Now, I checked the documentation up and down, including for example this page:

http://ift.tt/1PXd3em

I don't see anything about expected exceptions here.

Oh, and also, I'm using a fresh install of SQL Server 2014. The tables have nothing unusual or out of the ordinary about them.

I'm hoping someone knows what might cause the Drop() method from executing against a login that is known to exist.

EDIT

I'm able to delete the logins via the GUI with no problems, and the Powershell operation occurs while I'm logged in to the Server. My purpose is a customized bulk operation.





Aucun commentaire:

Enregistrer un commentaire