.NET Musings

So, I run into this problem every so often. I have a SQL Server instance that I need to restore a database to (e.g. for migrating through environments).

When you do a simple straight backup and restore, the users come with the data. That's great, since you don't have to worry about scripting them out, resetting permissions, etc - unless the user don't already exist on the server itself.

This causes orphaned user. You can't (or don't want to) delete them from the databases, but creating a user with the same name doesn't fix the problem, since the name is meaningless to the internals. All that matters is the security identifier (SID), which is unique for every user created (regardless of the name used).

To fix this, I call one of my two trusty DBA friends who know way more about SQL Server than me. Props to Matt Rigling, director of the SQL Server Users' Group in Cincinnati and fellow consultant as Pinnacle Solutions Group, Inc., for knowing most riki-tik what I needed.

By running the following in the newly restored databases:

EXEC sp_change_users_login 'Auto_fix', '[orphaned user]', null,'[orphaned user's password]'

it will add (the first time through) or link (for subsequent runs) the appropriate records in the Master database users table.

Thanks, Matt, for saving my butt!

Next, using a Master DB Stored Procedure to fix all orphaned users for a database...