Introduction
Over the years, I have had to provide information about logins and database users, most of the time per request of an auditor. Many times, this is very easy to accomplish because the login name matches the name of the database user account. If you look at the "New User" screen, you can see that I am able to enter a different User Name.
Because of this, I can have a User Name that doesn't match the Login Name. From an audit perspective, this can create some confusion. More importantly, it can make it difficult to provide accurate information to the auditors when asked.
In order to connect a database user to a login, we will need to use the SID, Security Identifier. When you create a login on a SQL Server, SQL Server creates a SID, sometimes. So what do I mean sometimes? Well, if the login is a SQL login, SQL Server will create the SID, however, if the login is an Active Directory user or group, the SID will the be same as the SID in Active Directory.
In order to find the SID, we will need to use Syslogins. There is quite a bit of information in this view, however for this purpose, we really only need two columns, SID and Name.
- SELECT sid
- , name
- FROMsyslogins
If you run the above query your results will look similar to what is below. In this case, we are going to use an account name "BlogAcct".
In order to get the SID for a user account, we would run the following query in the database with the user account. The results are just below it:
- SELECT sid
- , name
- FROMsys.sysusers
If you thought the SID looks familiar you would be correct. The SID is the same as the login mentioned above. However, if you notice, the names are different. This login name is BlogAcct, while the User Account is Fred. Yet both have the same SID. So what does this mean? It means that the User Account Fred is mapped to the Login BlogAcct.
If you run this query in the database you are looking for the mapped logins, you will get a result set similar to the image below the query.
- SELECT u.sid AS'DatabaseUserSID'
- , u.name AS 'DatabaseUserName'
- , l.name AS'LoginName'
- , l.sid AS 'LoginSID'
- FROMsys.sysusers u
- INNER JOINmaster..syslogins l
- ON u.sid = l.sid
Notice that the SIDs match, but the names do not. If you were to run this code against a production database, you will probably see a much large data set. This would also return accounts that have matching names as well. If you want to only find the user accounts that have different names from the login, you will need to add a WHERE clause. Similar to below.
- SELECT u.sid AS 'DatabaseUserSID'
- , u.name AS'DatabaseUserName'
- , l.name AS'LoginName'
- , l.sid AS 'LoginSID'
- FROMsys.sysusers u
- INNER JOINmaster..syslogins l
- ON u.sid = l.sid
- WHERE u.name <> l.name
When you do run this, you will see something that might be unexpected. The SA login comes back as having the same SID as the DBO. This is showing that the SA login is mapped to the dbo user account for all databases.
Hopefully, this might make it easier when auditors come knocking at your door looking for accounts with database access. This query will also work for AD groups as well.
Thanks for visiting my blog!