A union of curiosity and data science

Knowledgebase and brain dump of a database engineer


Fixing Orphaned SQL Server Users. SQL 2k, 2k5

After you transfer logins and passwords to the destination server, users may be unable to access the database. Logins are associated to users by the security identifier (SID), and if the SID is inconsistent after you move a database, SQL Server may deny the user access to the database. the logn names can be the same but the underlying guid/sid is different.
 
Report the orphans for your knowledge:

    EXEC sp_change_users_login 'Report'

If you already have a user with the same name and you know what the issue is....

    EXEC sp_change_users_login 'Auto_Fix', 'db_user'