When talking with people about databases and disaster recovery our conversation often sounds like this:
Q: What does your backup process look like? A: We send our backups off-site every night. Q: What about login information? A: Usernames & passwords are stored in a cloud-based password app. Q: What about SIDs? A: ??? What do you mean?
Security Identifiers (SIDs) for user account logins are a critical, and often overlooked, component of the user-server-database relationship. Usernames and passwords allow users to log in to SQL servers, but SIDs are what connect the logins to their databases.
A SQL server’s list of Logins can be found by querying the master.sys.sql_logins catalog view. Similarly, each database’s User information can be found in the database’s sys.database_principals catalog view. Note: The sys.syslogins view also contains login information, but is only intended for backwards compatibility.
If the SID associated with a SQL Login (ie username) matches the SID associated with a SQL User in a database, then that Login is granted the database roles & permissions associated with the User account.
When a server login is dropped and recreated (or if the database is moved to a new server and a new login is created) the login will have a new SID that won’t match the database user’s SID. Even though the login’s name and the user’s name match, the login will not be granted the user’s permissions to the database.
To ‘relink’ the SIDs associated with a login and user, the database user’s SID can be updated to reflect the new login’s SID. This can be accomplished by using the sp_change_users_login stored procedure.
However, in disaster recovery and server migration situations (ie when dozens or hundreds of logins are having to be recreated), having to run sp_change_users_login for each user in every database can be a time-consuming process.
A simpler solution is to include the database user’s SID when recreating a SQL server login. Then the login will be automatically ‘relinked’ to the user, and running sp_change_users_login isn’t necessary.
Taking the sp_change_users_login step out of the login recreation process makes the disaster recovery process a little easier, a little quicker, and a little less stressful.
So, when you’re adding SQL usernames & passwords to the password vault… don’t forget the SIDs.