SQL Server and Active Directory – How did I get here ?

I\’ve recently been asked to determine which groups give a user access to a database.

 \”Simple.\” I hear you cry. \”Just look at the logins on the server, and compare those with the groups the user is in in Active Directory.\”

 But what if there\’s no match ? For us DBAs, it\’ll usually be  a group mapped to SysAdmin, so no challenges there. But what if it\’s someone else who has several levels of nested AD groups, only one of which actually grants SQL access way down the tree ?

 I found a few security DMVs to help out here, sys.login_token and sys.server_principals.

 I think it works like this:

 When you add a login to SQL Server FROM WINDOWS , it will check the name given against AD, get the SID associated with the group, and add that (as well as some other stuff) to sys.server_principals

 When you connect to SQLServer as a user, your request contains all of the names (and SIDs) that you\’re a member of. All of them. Even the nested ones.

 (You can try this out by connecting to any instance of SQL using Windows Authentication, and executing SELECT [name] FROM sys.login_token  – you\’ll get more rows back than groups that you\’re a direct member of in AD)

 SQL Server then has to match up any of the SIDs in your request with those in sys.server_principals. If it finds one, you\’re in – if not, you can\’t authenticate any further and you get access denied.

 So, going back to my original request, I got the end-user to execute the following query:

 SELECT DISTINCT lt.[name] FROM sys.login_token lt

       INNER JOIN sys.server_principals sp

               ON lt.[sid] = sp.[sid]

 They then mailed me the results of this, and I could immediately see that they were a member of 2 groups that had access to the required instance. Their issue was that they appeared to be entering dates in both British and US formats in different databases – a problem easily diagnosed to the language settings of the groups that they were in
 (one was British English, the other English, and I don\’t need to tell you what that means.), but to track that down using SQL and AD alone would have taken quite some time.

 If you need to find out which groups grant you access to SQL Server, use the script above. No special permissions are required to use these DMVs, so anyone with access to Management Studio can use it to find our which groups grant them access. If the user doesn\’t have access to Management Studio, then it\’s not a big job to create either a vbScript or exe that executes the statement above (but you\’re on your own here – unless you\’re really stuck, in which case I\’ll help out if I can)