Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Identifying the Tie Between Logins and Users


By:   |   Last Updated: 2012-05-23   |   Comments (6)   |   Related Tips: More > Security

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.

Different names but same SID

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:

results returning different names but same SID
Next Steps


Last Updated: 2012-05-23


get scripts

next tip button



About the author
MSSQLTips author K. Brian Kelley K. Brian Kelley is a SQL Server author and columnist focusing primarily on SQL Server security.

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Thursday, February 18, 2016 - 9:56:02 AM - H Back To Top

Brian Kelley took the time to share a bit of his experience. He could have kept it for himself as we, in general, ot of laziness tend to do. Thank you Mr. Kelley

 

 


Tuesday, May 05, 2015 - 6:44:43 PM - Kathy Back To Top

Great explanation for fairly non-technical reader. Finally, I understand and there is a way to make the connection automatically.


Friday, March 14, 2014 - 12:25:34 PM - Leiah Back To Top

I ran the query shown below but identified mismatches between the SQL Logins and the Database Users, meaning that if I ran sys.database_principals and compared its results with the results of the query below, I couldn't find all users:

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

There were some users as part of the query result that I couldn't find in sys.database_principals and vice versa. Also, not all SQL Logins for which my database is the default database could be tied to a DB user. Why does this happen?


Thursday, November 21, 2013 - 3:10:38 AM - Shiva Back To Top

It helped me. Thnaks for giving query.


Wednesday, May 23, 2012 - 11:22:35 PM - James Back To Top

 

@Archana No difference at all. You could try this yourself you know.


Wednesday, May 23, 2012 - 11:29:01 AM - Archana Back To Top

Hello,

Archana, can anyone let me know the difference between 2005 and 2008 installation?

 

your suggestion must be appreciated.

 

thanks,

Archana

 


Learn more about SQL Server tools