SQL Authentication Via AD Groups Part II: Who has what access?

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:

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.

Run against sys.server_principals to generate a table full of xp_logininfo commands, one for each qualifying Windows login.

Iterate through that table, executing that command and inserting a row. Note: I tried to do this without a cursor (for who does not fear the wrath of Jeff Moden), but if any SQL logins are deleted from Active Directory, I am unable to execute a "giant string" command since it will fail on xp_logininfo with error 0x534 for any orphan SQL logins that are no longer in Active Directory. I got around this issue with a TRY...CATCH block that notates the orphaned SQL login.

Run an insert to obtain SQL logins so that their permissions can be reported as well.

I then get the Server Roles for every login and concatenate them into a single column for readability.

Run sp_msforeachdb to accumulate database users from sys.database_principals into a temporary table.

Process the table generated in step 3 above to concatenate the various roles for each user to a single column for readability.

Select out the contents of the final table.

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.

Account Name

Database Server

Database Name

LoginType

Server_Roles

Permission Path

User Privileges

DBGrowth

SERVER1

DBGrowthLog

SQL_USER

public

DBGrowth

db_datareader, db_datawriter

DBGrowth

SERVER1

DB_Census

SQL_USER

public

DBGrowth

db_datareader, db_datawriter

ACME\Joey

SERVER1

Servicedesk

WINDOWS_GROUP

public

ACME\Servicedesk_RWX

db_datareader, db_datawriter

ACME\Jimmy

SERVER1

Servicedesk

WINDOWS_GROUP

public

ACME\Servicedesk_RWX

db_datareader, db_datawriter

StupidApp1

SERVER1

tempdb

SQL_USER

sysadmin, dbcreator

StupidApp1

db_owner

App2

SERVER1

Servicedesk

SQL_USER

public

App2

db_owner

SSRS_SDP_RO

SERVER1

DBGrowthLog

SQL_USER

public

SSRS_SDP_RO

db_datareader

SSRS_SDP_RO

SERVER1

DB_Census

SQL_USER

public

SSRS_SDP_RO

db_datareader

SSRS_SDP_RO

SERVER1

Servicedesk

SQL_USER

public

SSRS_SDP_RO

db_datareader

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