Identifying the Tie Between Logins and Users in SQL Server

Problem

Recently I was trying to find out how someone had access to a particular database in SQL Server. When I looked at the list of users at the database level, I didn't find any that matched up either with the user's Windows account or with any of the Windows groups the user belonged to. I included nested groups in Active Directory when checking this out. I found out later that there was a tie, however, the login at the server level did not match the user at the database level. I didn't realize this was permitted. How can I check for this in the future?

Solution

Each login to SQL Server has a unique identifer, think like a primary key, to identify that login from every other login. This is true whether the login is a SQL Server-based login, a Windows user, or a Windows group. That unique identifier is called the SID, which is short for security identifier. In the case of a SQL Server-based login, the SID is generated by SQL Server. For Windows users and groups, the SID matches the SID in Active Directory.

When a login is mapped into a database, the SID is used to tie together the login and the user. Since it is the SID thats important, so far as SQL Server is concerned, the name for the login can be different from the name for the user. Generally speaking, it is better to keep them the same. If they are different, then you can have the same sort of confusion you just experienced, and when it comes to security, that's bad. So it would have to be a special case for me to consider having a database user tied to a server login with different names. 

With that said, you can certainly see this in practice if you have a test SQL Server. The first thing to do is to create the login at the server level. Run the appropriate code snippet based on your version of SQL Server:

-- SQL Server 2005+
CREATE LOGIN [NotTheSame] WITH PASSWORD = 'SomeStr1ctP4ssw0rd!';
GO 

-- SQL Server 2000
EXEC sp_addlogin @loginame = 'NotTheSame', @passwd = 'SomeStr1ctP4ssw0rd!';
GO

Then select a test database and create the user. Again, choose the appropriate code snippet based on your version of SQL Server:

-- SQL Server 2005+
CREATE USER [ADifferentName] FROM LOGIN [NotTheSame];
GO 

-- SQL Server 2000
EXEC sp_grantdbaccess @loginame = 'NotTheSame', @name_in_db = 'ADifferentName';
GO

If we run a query where we join the appropriate catalog views or tables together based on SID, we should see the connection:

-- SQL Server 2005+
SELECT d.[name] AS 'DB User', d.sid AS 'DB SID',
 s.[name] AS 'Login', s.sid AS 'Server SID'
FROM sys.database_principals d
 JOIN sys.server_principals s
 ON d.sid = s.sid
WHERE d.[name] = 'ADifferentName';

-- SQL Server 2000
SELECT d.[name] AS 'DB User', d.sid AS 'DB SID',
 s.[name] AS 'Login', s.sid AS 'Server SID'
FROM sysusers d
 JOIN master..syslogins s
 ON d.sid = s.sid
WHERE d.[name] = 'ADifferentName';
GO 

For instance, here's the result. Note that the SID for the user and the SID for the login are the same.

Searching for the General Case

When you have an issue finding a mapping for a given database, a simple query can return the cases where the names are mismatched. The following queries should always return at least one row because of the dbo user. It maps to the owner of the database and naturally this won't be "dbo" as the name.

-- SQL Server 2005+
SELECT d.[name] AS 'DB User', s.[name] AS 'Login', s.sid AS 'SID'
FROM sys.database_principals d
 JOIN sys.server_principals s
 ON d.sid = s.sid
WHERE d.[name] <> s.[name];

-- SQL Server 2000
SELECT d.[name] AS 'DB User', s.[name] AS 'Login', s.sid AS 'SID'
FROM sysusers d
 JOIN master..syslogins s
 ON d.sid = s.sid
WHERE d.[name] <> s.[name];
GO 

For instance, when I run it on the database where the user was just created, here are the results:

 

Source collected from MSSQLTIPS.COM