Fixing Orphaned Users – the PowerShell/SMO way – NOT!

I’ve been working with quite a few PowerShell/SMO scripts for the last little while, and so far so good. Just recently I needed to map some orphaned users, and I thought – why not? Let’s do it in PowerShell.
This would have been the script that *should* work:

#unfortunately this doesn't work
$user.Login = "marymargaret";

This throws an exception – to my surprise. I thought this should be quite straightforward. The exception I get is:

Exception calling "Alter" with "0" argument(s): 
"Alter failed for User 'marymargaret'. " 
---> Microsoft.SqlServer.Management.Smo.FailedOperationException: 
Alter failed for User 'marymargaret'.  
---> Microsoft.SqlServer.Management.Smo.SmoException: 
Modifying the Login property of the User object 
is not allowed. You must drop and 
recreate the object with the desired property.

So, in a gist, the exception requires me to drop and recreate the user. In a development environment maybe. But I definitely do not want to go this route, when I don’t have to. It will be cumbersome to always re-assign permissions and roles to this user. It’s a shame I can’t accomplish fixing orphaned using strictly SMO.

The solution would be to use the T-SQL ALTER statement with the Invoke-SqlCmd cmdlet:

$query = "ALTER USER $username WITH LOGIN=$loginname";
Invoke-Sqlcmd -ServerInstance $instanceName -Query $query -Database $databasename

You might still be tempted to use sp_change_users_login. Don’t give in to temptation. There’s a clear warning on the MSDN pages – this will go away. Soon.

By the way, can you tell I’ve been watching too much Once Upon a Time. I love the show, but I don’t want it to get too complicated. Oh Charming, just be with Snow already!

Leave a Reply

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

You are commenting using your 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 )

Connecting to %s