Restoring Usernames after restore

SamC

After a restore, using SQL Backup or other, the usernames need to be deleted and reassigned from the server's pool of usernames.

Is there any way to avoid this? If not, any single button solution to this problem?

Sam

Brian Donahue

Hi Sam,

There is no way around this. If you restore a database backup taken from a different server, the Security Identifier for the users in the database will not match the usernames in SQL Server's Security container even if the usernames are the same.

This is a fairly common dilemma, though, so there is a solution built into SQL Server that will reconcile a Security user SID to a database user SID. If you open Query Analyzer, you can run

USE MyDatabase
EXEC sp_change_users_login 'Update_One', 'MyUser', 'MyUser'

This will link the SID for the MyUser security account to the MyUser database user in the MyDatabase database so that he can log in again.