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!