Options

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
· Share on Twitter

Comments

  • Options
    Brian DonahueBrian Donahue Posts: 6,590 Bronze 1
    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.
    · Share on Twitter
Sign In or Register to comment.