Drop login Powershell SMO

For some reason, Powershell is what I’m blogging about these days.  Maybe it is rare to use it in this way, so there is not much information out there.  So I want to be a help to others who need the answers.

I developed a process to drop temporary logins, but discovered a problem recently.  It could not drop the login because of the following error:  Detailed error:Could not drop login ‘LoginName’ as the user is currently logged in.  The individual using the login apparently doesn’t close their SSMS.

There doesn’t seem to be a streamlined way in SMO to drop all connections for that specific login.  But this blog post showed me the EnumProcesses() function, which allowed me to kill the connections for that login.

Without automating anything, here are the simple commands.

Search for connections/processes with login ‘cat’
$ServerObject.EnumProcesses() | Where-Object {$_.Login -eq “cat”}

SMOProcesses

Kill connections manually from above picture
$ServerObject.KillProcess(54);
$ServerObject.KillProcess(68);

For the automated piece, a typical foreach loop is fine

$SqlLoginName = “cat”;
$ConnectionsToDrop = $ServerObject.EnumProcesses() | Where-Object {$_.Login -eq $SqlLoginName};
foreach($ConnectionToDrop in $ConnectionsToDrop)
{  $ServerObject.KillProcess($ConnectionToDrop.Spid); }

Advertisements
This entry was posted in Uncategorized. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s