Update Orphaned MSSQL Users

21 Jan 2009 in Systems administration on Security, Sql, Stored procedures, Mssql, Windows

I’ve been moving the databases at work from one Microsoft SQL 2005 server to another SQL 2005 Server. When the databases grant security to non-domain users, they have a tendency to not be attached / accessible on the new server. What you have to do is reattach / rejoin the orphaned security authorization to the credential that you reestablish on the new SQL Server. This is done with the following pl_Sql command:

sp_change_users_login 'Auto_Fix'
sp_change_users_login 'Update_One', 'old_username', 'new_username'

Auto_Fix can only be used if the old_username and the new_username are the same.