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?
You could set up a login trigger or set up SQL Server Auditing to audit logins.
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
1 Like