It's a situation that can leave database administrators scratching their heads: a user who should be able to access a database suddenly can't. They're not blocked, they're not locked out in the usual sense. They're what we call 'orphaned users' in SQL Server.
Think of it like this: a database user is essentially a persona within a specific database. This persona is usually tied to a master login on the SQL Server instance itself. This login is the key that authenticates the user to the server. When that master login disappears – perhaps it was deleted, or maybe the entire database was moved to a new server where that login never existed – the database user is left without its anchor. It's still there, its permissions intact within the database, but it has no valid identity on the server to prove it's who it says it is. Hence, 'orphaned'.
This can happen for a few common reasons. The most straightforward is when a login is simply dropped from the master database. Even if you recreate the login later, it often gets a new Security Identifier (SID), and the database user, still holding onto the old SID, becomes orphaned. Another frequent culprit is moving a database. If you detach a database from one server and attach it to another, the logins that were previously mapped to its users might not exist on the new server. The database user is there, but its corresponding server login isn't.
Now, the old way of fixing this involved a stored procedure called sp_change_users_login. However, Microsoft has deprecated that. The modern, recommended approach is to use the ALTER USER ... WITH LOGIN statement. It's a cleaner, more direct way to re-establish that crucial link.
So, how do you even find these digital castaways? For SQL Server and PDW, you can run a query directly in the user database. It involves a LEFT JOIN between sys.database_principals (which lists your database users) and sys.server_principals (which lists your server logins), looking for users whose SIDs don't match any server principal. If sp.sid IS NULL and the user's authentication type is INSTANCE, you've likely found an orphaned user.
For those working with Azure SQL Database or Azure Synapse Analytics, the process is a bit different because sys.server_principals isn't directly accessible in the same way. You'll need to connect to the master database first to get a list of all login SIDs, and then query your user database's sys.database_principals table. Comparing these two lists will reveal any SIDs present in the user database but missing from the master database logins.
Once you've identified an orphaned user and its SID, the fix is to recreate the login in the master database, but crucially, you need to use the WITH SID = 'your_sid_here' option. This ensures the new login has the exact same SID as the orphaned user, effectively re-establishing the connection and bringing the user back into the fold. It’s a bit like giving them back their original key, rather than a new one.
It's worth noting that to minimize the chances of this happening in the first place, especially for databases that might be moved around, consider using contained database users. These users authenticate directly within the database itself and aren't tied to server logins, making them inherently more portable and immune to orphaning. It's a different model, and might not fit every scenario, but it's a powerful tool for portability.
