Re-associate SQL users with logins

I’ve been getting our test environment in sync with our production environment the past week. Part of this effort involves moving database from our production environment to our test database server. The easiest way to do this is to put the production server into single-user mode, copy the .MDF and .LDF database files over to the test database server, and the attach the database files to the test database server.

The unfortunate side effect of this is that the SQL users in each database are no long associated with the SQL Login. SQL Server has had an sp called ‘sp_change_users_login‘ since SQL Server 2000. You can pass in the ‘Auto_Fix’ parameter and the name of the SQL user that is orphaned and SQL Server will do it’s best to automatically match the user name up with a SQL login. There are a few gotchas that the MSDN article, and people better versed in SQL Server internals than I, can spell out. But for 90% of the cases out there, this SP will work fine. But it only works on one user at a time. So I created the world simplest code generator to help me create all of the statements needed to attempt to reassociate my users with their logins.

select ‘exec sp_change_users_login ”Auto_Fix”,”’ + name + ”” from sysusers where uid > 2 and uid < 16000 [/sql] uid 2 is the guest account and uids > 16000 are the built in “db_*” users.