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!

No related posts.