SQL Server 2005: Backup, Restore, and Orphaned Users
by bhartsock on Oct.28, 2008, under Uncategorized
Backing up a SQL Server database and restoring it to another server is a common operation done for reporting, testing, or development. When restoring on a different machine, a common problem is the login principals don’t actually correlate to the restored database users. This is because they are linked by an SID. This SID isn’t the user name, its some hash value, probably a GUID. The problem is fully described in this article.
Fixing the problem is really simple. Just run the following SQL and substitute
USE <database_name>; GO sp_change_users_login @Action='update_one', @UserNamePattern='<database_user>', @LoginName='<login_name>'; GO
2 Comments for this entry
Leave a Reply
Twitter: brianhartsock- In ATL meeting some @RackApps customers. Very insightful and refreshing to meet face to face.
- It's spring already in GA. Great weather.
- I recommend sticking with WRT54G with DD-WRT installed. The new Linksys routers are evil.
- Setting up a router on a Saturday afternoon was a bad idea. Relaxing Saturday turned extremely frustrating.
- Unintended consequences - Transactions - http://tinyurl.com/yjtulm3
January 31st, 2009 on 2:02 am
This is so stupid, how long do you think this will take you if you have 300 users.
January 31st, 2009 on 9:54 am
You can script it in cases like that. Or, when creating users, you can specify the SID so they can be the same on every server so you don’t have this problem.