On December 21, 2015, my article, Liberating the DBA from SQL Authentication with AD Groups appeared on this wonderful site, and I received a significant response!  One of these responses stuck in my head, and drove me to do a little research. The question was this: If you have these groups, and your sysadmins will not allow you even the read-only access to the AD groups, how can you know who has access to your databases?

As soon as the question popped up, so did the need for this follow-up article. Does the DBA even care about this? If the DBA has any concern about the data for which he or she cares, he or she had better care deeply! Moreover, auditors won't be able to catch every nuance of every AD group. I can imagine the DBA rightly fearing that some snafu in a AD group accidentally gives Bill the Janitor access to the CEO's compensation package, or a junior programmer access to the payroll rate table.  Nightmare scenario: 

update [PayRateTable]
 set PayRate = PayRate * 500
 where employee_name = 'Doofus DoesNotNeedAccess';

Does the audit tool catch this before the next set of paychecks go out and Mr. Doofus is flying first-class to a non-extradition country? Does it even matter that the DBA did not have any input into the process that grants the wrong access? Someone's head will roll, and it might be yours.

The better thing to do is to query SQL Server to find out who has access to what.

How to Query SQL Server to See Individuals with Access

The query shown and attached to this article works at the database level to identify users, their permissions, and how they came to get those permissions - which AD Group or Groups give them the access. The DBA does not need to have any enhanced AD permissions in order to obtain the results listed here.  SQL Server has these permissions, because it needs them in order to effectuate Integrated Security.  The scripts are tested on SQL Server 2005 through 2014 SP1.

At the database level, we can use a query script to iteratively run the xp_logininfo stored procedure on sys.server_principals that are windows logins and groups (CAVEAT; xp_logininfo does not work on "NT SERVICE\" accounts, even though it registers to SQL as a WINDOWS_GROUP - that is probably okay; these "groups" are local to the machine and do not usually have members). Some auditors have been using similar scripts for years; I took scripts I've used before and improved them into one single script that produces an informative result set that will tell the DBA who has what access.

The query script below also retrieves SQL logins and lists their permissions by database, in order to give a server-wide understanding of what login can do what.  This is important for applications that demand a SQL Login.  Now I still believe that, if the connection string is not hard-coded in the program (Yipes!), it should be possible to migrate to integrated security and that the work to do so is important to a cogent cybersecurity strategy.

Here is the script (which is also attached):

------------------------------------------------------------
-- The SQLBlimp AD Access Identification Script
-- By John F. Tamburo 2016-01-06
-- Feel free to use this - Freely given to the SQL community
------------------------------------------------------------
set nocount on;
declare @ctr nvarchar(max) = '', @AcctName sysname = ''
-- Create a table to store xp_logininfo commands
-- We have to individually execute them in case the login no longer exists
create table #ExecuteQueue(AcctName sysname,CommandToRun nvarchar(max));
-- Create a command list for windows-based SQL Logins
insert into #ExecuteQueue(AcctName,CommandToRun)
SELECT 
[name]
,CONVERT(NVARCHAR(max),'INSERT INTO #LoginsList EXEC xp_logininfo ''' + [name] + ''', ''all''; --insert group information' + CHAR(13) + CHAR(10)
+ CASE 
WHEN [TYPE] = 'G' THEN ' INSERT INTO #LoginsList EXEC xp_logininfo  ''' + [name] + ''', ''members''; --insert member information'  + CHAR(13) + CHAR(10)
            else '-- ' + rtrim([name]) + ' IS NOT A GROUP BABY!' + CHAR(13) + CHAR(10)
        END) as CMD_TO_RUN
FROM sys.server_principals 
WHERE 1=1
and TYPE IN ('U','G')    -- *Windows* Users and Groups.
and name not like '%##%' -- Eliminate Microsoft 
and name not like 'NT SERVICE\%' -- xp_logininfo does not work with NT SERVICE accounts
ORDER BY name, type_desc;
-- Create the table that the commands above will fill.
create table #LoginsList(
       [Account Name] nvarchar(128),
       [Type] nvarchar(128),
       [Privilege] nvarchar(128),
       [Mapped Login Name] nvarchar(128),
       [Permission Path] nvarchar(128) );
-- Jeff Moden: Please forgive me for the RBAR! (:-D)
declare cur cursor for select AcctName, CommandToRun from #ExecuteQueue
open cur
fetch next from cur into @AcctName,@ctr
while @@FETCH_STATUS = 0
begin
BEGIN TRY
print @ctr
EXEC sp_executesql @ctr
END TRY
BEGIN CATCH
    print ERROR_MESSAGE() + CHAR(13) + CHAR(10);
IF ERROR_MESSAGE() like '%0x534%' -- Windows SQL Login no longer in AD
BEGIN
print '0x534 Logic'
insert into #LoginsList([Account Name],[Type],[Privilege],[Mapped Login Name],[Permission Path])
select @AcctName AccountName,'DELETED Windows User','user',@AcctName MappedLogin,@AcctName PermissionPath
END
ELSE
print ERROR_MESSAGE();
END CATCH
fetch next from cur into @AcctName,@ctr
Print '-------------------------------'
END;
-- Clean up cursor 
close cur;
deallocate cur;
-- Add SQL Logins to the result
insert into #LoginsList([Account Name],[Type],[Privilege],[Mapped Login Name],[Permission Path])
select [name] AccountName,'user','user',[name] MappedLogin,[name] PermissionPath
FROM sys.server_principals 
WHERE 1=1
and (TYPE = 'S'     -- SQL Server Logins only
and name not like '%##%') -- Eliminate Microsoft 
or (TYPE in('U','G') and [name] like 'NT SERVICE\%') -- capture NT Service information
ORDER BY [name];
-- Get Server Roles into the mix
-- Add column to table
alter table #LoginsList add Server_Roles nvarchar(max);
-- Fill column with server roles
update LL 
set 
Server_Roles = ISNULL(STUFF((SELECT ', ' + CONVERT(VARCHAR(500),role.name)
FROM sys.server_role_members
JOIN sys.server_principals AS role
ON sys.server_role_members.role_principal_id = role.principal_id
JOIN sys.server_principals AS member
ON sys.server_role_members.member_principal_id = member.principal_id
WHERE member.name= (case when [Permission Path] is not null then [Permission Path] else [Account Name] end)
FOR XML PATH('')),1,1,''),'public')
from #LoginsList LL;
-- Create a table to hold the users of each database.
create table #DB_Users(
DBName sysname
, UserName sysname
, LoginType sysname
, AssociatedRole varchar(max)
,create_date datetime
,modify_date datetime
)
-- Iterate the each database for its users and store them in the table.
INSERT #DB_Users
EXEC sp_MSforeachdb
'
use [?]
SELECT ''?'' AS DB_Name,
ISNULL(case prin.name when ''dbo'' then prin.name + '' (''+ (select SUSER_SNAME(owner_sid) from master.sys.databases where name =''?'') + '')'' else prin.name end,'''') AS UserName,
prin.type_desc AS LoginType,
isnull(USER_NAME(mem.role_principal_id),'''') AS AssociatedRole ,create_date,modify_date
FROM sys.database_principals prin
LEFT OUTER JOIN sys.database_role_members mem ON prin.principal_id=mem.member_principal_id
WHERE prin.sid IS NOT NULL 
and prin.sid NOT IN (0x00) 
and prin.is_fixed_role <> 1 
AND prin.name is not null
AND prin.name NOT LIKE ''##%'''
-- Refine the user permissions into a concatenated field by DB and user
SELECT
dbname
,username 
,logintype 
,create_date 
,modify_date 
,STUFF((SELECT ', ' + CONVERT(VARCHAR(500),associatedrole)
FROM #DB_Users user2
WHERE user1.DBName=user2.DBName 
AND user1.UserName=user2.UserName
FOR XML PATH('')),1,1,'') AS Permissions_user
into #UserPermissions
FROM #DB_Users user1
where logintype != 'DATABASE_ROLE'
GROUP BY
dbname
,username 
,logintype 
,create_date 
,modify_date
ORDER BY DBName,username
-- Report out the results
Select 
DISTINCT
LL.[Account Name]
,@@SERVERNAME as [Database Server]
,UP.dbname as [Database Name]
,LoginType
--,LL.Privilege
,LL.Server_Roles
,LL.[Permission Path]
,UP.Permissions_user as [User Privileges]
from #LoginsList LL
left join #UserPermissions UP
on LL.[Permission Path] = UP.UserName
-- Comment out the where clause to see all logins that have no database users
-- and their server roles.
-- where exists(select 1 from #LoginsList U2 where U2.[Account Name] = UP.[UserName])
order by
LL.[Account Name]
,UP.DBName;
-- Clean up my mess
drop table #ExecuteQueue;
drop table #LoginsList;
drop table #DB_Users;
drop table #UserPermissions;

The general idea is:

Please Note: There is a where clause in the final select.  If it is not commented out, you will see only the SQL logins that have database users attached.  Commenting it out will give you all SQL logins that have no database users, as well as their server roles.

Example Result:

I ran this on a server and cleaned the data so that there is nothing recognizable.

We see the user, server, database name, what kind of login it is, server roles, how he or she got access, whether the login is a user or a sysadmin, and the group by which the user got the listed permissions.

A couple of Caveats...

• For the sake of readability, I am not joining in object-level permissions.
• The script above works in SQL 2005-present.
• This script can easily be modified to feed a small "permissions warehouse" where one can slice and dice the permissions at any level chosen in order to figure out what any one person can do to any one database, or to see if the security admins accidentally made the janitor a sysadmin.

Conclusion

SQL Server's AD groups authentication is a gigantic help to the DBA.  It makes securing one's databases much easier. The above script gives the DBA visibility to the end users and the access that they enjoy, so that any problems can be remediated before they are discovered by auditors or a crafty end-user.

-----

John F. Tamburo is the Chief Database Administrator for Landauer, Inc., the world's leading authority on radiation measurement, physics and education. John can be found at @SQLBlimp on Twitter.  John also blogs at www.sqlblimp.com