SQLTeam.com | Weblogs | Forums

How to Identifying the Tie between logins and users?

sql2008r2

#1

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?


#2

You could set up a login trigger or set up SQL Server Auditing to audit logins.


#3

Just to confirm it, in SQL Server, the user name can be anything, no matter what the login name is. For example, this is perfectly valid:

CREATE USER xxx FROM LOGIN special_login1;

SQL links the db user to the system login using the SID. Therefore, if you want to see if a user name does not match a login name, you can use this query:

SELECT dp.name AS user_name, sp.name AS login_name, dp.*, sp.*
FROM sys.database_principals dp
INNER JOIN sys.server_principals sp ON sp.sid = dp.sid
WHERE dp.name <> 'dbo' AND dp.name <> sp.name