Here’s a good starter article on SQL Server logins, users, and SIDs: SQL Server Logins, Users and Security Identifiers (SIDs)
Some major points to absorb from this:
- A login is created at the SQL Server instance level. We say it’s an instance-level object.
- A user is created at the database level. It’s a database-level object.
- The SID is the server ID – a thing that uniquely identifies a login.
- The SID is what associates a user with a login. So the login Acct1 on ServerA might have SID = 0x01050000000023051500000082673CDCF4B779E75761DEE48E640000, and when we create an “Acct1” user for DB1, it’ll have that same SID.
- That’s why a user can be orphaned when we restore a backup to another server; the login may not exist at all on that new server, or it might exist with a different SID. (We detect and fix orphaned users with the built-in procedure sp_change_users_login.)
- Finally, when you create a login FROM WINDOWS (an account that exists in Active Directory, like MyDomain\Jen), it gets assigned the SID that already exists in AD. Therefore, Windows users can never be orphaned…just SQL users (like “ReportUser”).
Short and sweet. Good stuff!
Well, I wouldn’t say that windows accts can never be orphaned, but it’s much more unlikely.
If someone deleted the windows acct from AD, recreated another acct with the same name, and then had to drop the SQL login to get them to be able to log into SQL, then yeah, an individual DB user can be orphaned.
I’ve actually seen this. Someone quit and their acct was deleted from AD. Of course, they didn’t tell the DBA team so we didn’t remove them from SQL. A month or so later they came back to the company only this time when their acct was recreated with the same name, it was given a new SID in AD. That invalidated their current SID in SQL and we had to drop/recreate their login on every SQL box.