Determining Active Directory Group Membership

As discussed in my previous post I prefer to use Windows authentication over SQL authentication on my SQL Servers.  There are however two things that I would recommend controlling which is Active Directory Group membership and correct Active Directory design.

Let’s say you have a tiered Active directory group structure comprising of three Global Security groups Development Team 1, Development Team 2 and Development Team 3.  Development Team 1 whose members are your most senior, respected and trusted developers and as such have elevated permissions on some SQL Servers and restricted access to others which lesser groups (Development Team 2 and Development Team 3) do not.

The issues arise because you are not responsible for assigning group membership or the Active Directory design this is controlled by the IT Operations team.  As a result there is nothing stopping a manager requesting membership in Development Team 1 for a developer who should not have this level of access.  Now in this scenario the request for membership in the group was not made for access to certain SQL Servers or for elevated permissions on certain SQL Servers it was made for access let’s say to a network resource.

The two points here are;

1 – Separate Windows and SQL Server permissions.

Take the Development Team tiered Active Directory structure, these groups make granting access to resources easier but and it’s a big BUT can also be misused whether intentionally or unintentionally.  What you should have here is two sets of tiered groups one for SQL Server and one for network resources this allows you to separate the permissions and prevent unnecessary access as in the above scenario, example groups below;

SQL – Development Team 1

SQL – Development Team 2

SQL – Development Team 3

Windows – Development Team 1

Windows – Development Team 2

Windows – Development Team 3

2 – Active Directory group membership visibility

I have had many scenarios over the years where I have required the need to obtain information from Active Directory as part of a routine and have used both CSVDE and LDAP, but there is a much easier method to view Active Directory group membership.  Now I can’t remember which site / blog I first saw this procedure on so I apologise.  The procedure in question is xp_logininfo which returns information about Windows users and Windows groups.

One use for this would be to create a routine which runs every x hours or days and reports changes since the last run.  This would give a more granular audit of who has access to your SQL Servers.  I will look at creating such a routine when time allows and post here.  In the meantime if anyone creates such a routine and would like to share it by all means get in touch and I will post it ckwmcgowan@gmail.com.

Cheers

Chris