How can we help you today? How can we help you today?

Restoring Usernames after restore

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
SamC
0

Comments

1 comment

Sort by
  • 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.
    Brian Donahue
    0

Add comment

Please sign in to leave a comment.