I think the best practice is that, whenever possible, you create an Active Directory group and grant that group permissions in your SQL Server. If you have twenty developers, I like seeing one group , i.e. [mydomain\it-developers] instead of twenty individual logins.

Over time however, things sometimes get a little disjointed, and you've added six, seven or more different Windows groups, and are never really sure who is in what group. The problem with this strategy is that someone that is not the DBA can add someone to an Active Directory group that gives him more access that you intended, since the AD user account can be in multiple groups. It's up to you, the DBA, to identify any AD Users that exist in multiple AD Groups, that might get inadvertently higher permissions.

My Real Life Scenario

So one day i'm just looking at the results of Adam Mechanic's sp_whoisactive and i see a specific business user's login running a query on the production database. But wait a minute, i know the AD group he is in, [mydomain\BusinessReporting], and I KNOW that group definitely doesn't have access to that database.

Since SQL Server doesn't change peoples permissions behind my back, right away, I know someone must have added him to one of the other groups. But which one? Did he get added to mydomain\SQL-DBA's?

The Quick Solution

I put together the script below quickly and then bullet proofed it a little more. The built in extended stored procedure, xp_logininfo, is the core.

That procedure has the ability to enumerate ADgroup details, or to enumerate ADLogin Details, depending on whether you pass in the two parameters. These are the typical examples I always use:

EXEC master..xp_logininfo @acctname = 'mydomain\lizaguirre',@option = 'all' -- Show all paths a user gets his auth from
EXEC master..xp_logininfo @acctname = 'mydomain\authenticatedusers',@option = 'members'     -- show group members

Simple and straight forward,  and I can manually enumerate any groups I want with this.

However, I'm not in the habit of running a line of code, modifying it, and running it again. I love filling the gaps with metadata instead. Everything I code needs to be written with automation in mind. And the code needs to work in all situations, so I have to bug-proof the code for any errors I encounter during testing.

The Grunt Work Details

It's time to break out the Legos! By Legos, I mean all the snippets and tricks I've learned over the years and put them all together. If I'm going to scan one to many AD groups, I'm going to need a cursor to create a loop to call the procedure multiple times. if I call a stored procedure multiple times, I'll have multiple data sets; but I want all the results in ONE dataset. To do that I need a temp table to capture the results.

So I put those pieces in place and discover there are some Windows groups cannot be enumerated. For various reasons, an error like this happens: 

Msg 15404, Level 16, State 5

Could not obtain information about Windows NT group/user 'NT SERVICE\MSSQLSERVER', error code 0x8ac.

This error stops my simple cursor, so I need to add a try-catch to handle that. I added print statements, so I can review the errors, and find out more!

I get five groups that I cannot enumerate, and your mileage may vary, obviously.

• NT SERVICE\MSSQLSERVER
• NT SERVICE\ClusSvc
• NT SERVICE\SQLSERVERAGENT
• MYDOMAIN\FTL-Accounting
• MYDOMAIN\SCP-SQL-Admins

Since I went to the trouble of grouping my results, I really want to group my errors too, since print statements are not really "in your face" enough for me to really be aware of. So I added a table variable, which gets it's results in the catch.

Finally, I added a few comments so that when I read this code again, six months from now, I know what I was thinking.

Give this a spin, and let me know what you think! I've attached the identical code as an attachment as well.

--###############################################################################################
-- Quick script to enumerate Active directory users who get permissions from An Active Directory Group
--###############################################################################################
--a table variable capturing any errors in the try...catch below
DECLARE @ErrorRecap TABLE
  (
     ID           INT IDENTITY(1, 1) NOT NULL PRIMARY KEY,
     AccountName  NVARCHAR(256),
     ErrorMessage NVARCHAR(256)
  ) 
IF OBJECT_ID('tempdb.dbo.#tmp') IS NOT NULL
  DROP TABLE #tmp
--table for capturing valid resutls form xp_logininfo
CREATE TABLE [dbo].[#TMP] ( 
[ACCOUNT NAME]       NVARCHAR(256)                        NULL ,
[TYPE]               VARCHAR(8)                           NULL ,
[PRIVILEGE]          VARCHAR(8)                           NULL ,
[MAPPED LOGIN NAME]  NVARCHAR(256)                        NULL ,
[PERMISSION PATH]    NVARCHAR(256)                        NULL )
 DECLARE @groupname NVARCHAR(256)
 DECLARE c1 cursor LOCAL FORWARD_ONLY STATIC READ_ONLY for
  --###############################################################################################
  --cursor definition
  --###############################################################################################
   SELECT name 
   FROM master.sys.server_principals 
   WHERE type_desc =  'WINDOWS_GROUP' 
   --###############################################################################################
  OPEN c1
  FETCH NEXT FROM c1 INTO @groupname
  WHILE @@FETCH_STATUS <> -1
    BEGIN
      BEGIN TRY
        INSERT INTO #tmp([ACCOUNT NAME],[TYPE],[PRIVILEGE],[MAPPED LOGIN NAME],[PERMISSION PATH])
          EXEC master..xp_logininfo @acctname = @groupname,@option = 'members'     -- show group members
      END TRY
      BEGIN CATCH
        --capture the error details
        DECLARE @ErrorSeverity INT, 
                @ErrorNumber INT, 
                @ErrorMessage NVARCHAR(4000), 
                @ErrorState INT
        SET @ErrorSeverity = ERROR_SEVERITY()
        SET @ErrorNumber = ERROR_NUMBER()
        SET @ErrorMessage = ERROR_MESSAGE()
        SET @ErrorState = ERROR_STATE()
       --put all the errors in a table together
        INSERT INTO @ErrorRecap(AccountName,ErrorMessage)
          SELECT @groupname,@ErrorMessage
         --echo out the supressed error, the try catch allows us to continue processing, isntead of stopping on the first error
        PRINT 'Msg ' + convert(varchar,@ErrorNumber) + ' Level ' + convert(varchar,@ErrorSeverity) + ' State ' + Convert(varchar,@ErrorState)
        PRINT @ErrorMessage
    END CATCH
    FETCH NEXT FROM c1 INTO @groupname
    END
  CLOSE c1
  DEALLOCATE c1
--display both results and errors
SELECT * FROM #tmp
SELECT * FROM @ErrorRecap