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

Written by belle
Posted February 13, 2012 at 12:04 am

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";
$user.Alter();
$user.Refresh();

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

System.Management.Automation.MethodInvocationException: 
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!

Rating: 10.0/10 (6 votes cast)

Rating: 0 (from 0 votes)

Fixing Orphaned Users – the PowerShell/SMO way – NOT!, 10.0 out of 10 based on 6 ratings

Be Sociable, Share!