Restoring Usernames after restore

SamCSamC Posts: 28
edited April 18, 2005 4:20PM in SQL Backup Previous Versions
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

Comments

  • Brian DonahueBrian Donahue Posts: 6,590 New member
    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.
Sign In or Register to comment.