Monday, 24 October 2011

User accounts go missing when restoring a backup on a different SQL Server


Backup and Restore are probably the most favourable mechanisms in the express version of SQL-Server, easily accessible through the management studio, easy to execute, and easy to understand. 

Except when all your users lose access to the server...

Each SQLServer database stores user accounts with SID's (a security identifier) which is generated upon account creation based on where the account itself is created. So, server A's version of a user with the same credentials will have a different SID from server B, ect...

Therefore, when restoring a database from a different server - you will unlink your login's because the SID's do not match, in turn creating "Orphaned Users"

So, it is good practice that after a restore you check if you have created "orphans" by using a stored procedure provided by Microsoft called "sp_change_users_login" in two steps:

  1. List off any orphaned logins:

    EXEC sp_change_users_login 'REPORT'

    (any results from that Query, are considered to be orphans.)
  2. Relink orphaned logins
    EXEC sp_change_users_login 'UPDATE_ONE','username','username'


I have to extend much gratitude for Google and Stack Overflow for being a programmers best friend and finding me a resolve to a problem that has been restricting me from moving data back and forth from developer to deployment servers. They of course leading me to Eric Johnson's detailed article on the matter - a must read - for anyone trying to better understand the issue.

More information on the "sp_change_users_login" stored procedure can be found at: http://msdn.microsoft.com/en-us/library/aa259633

Regards,

Alex Casamassima

No comments:

Post a Comment

Twitter

Page Hits