SQL/ Syndicated

Using xp_logininfo to Determine Membership in Active Directory Group

1 Comment

The preferred method of securing Microsoft SQL Server in the environments I support is via usage of Active Directory Groups.  We do this for numerous reasons, but when it comes right down to it we do so because it removes the Database Administrators from the ad nauseum grind of adding and removing users for our SQL Server databases.  (At least that is the reason I like best!)  It also allows our Security Team to centralize the responsibility of maintaining user access to applications, servers, databases, and in general all aspects of the information infrastructure of our medical facilities.  Not to mention usage of a trusted, authenticated connection to the domain is vastly superior to the simplistic login/password construct of SQL Server authentication.  It’s a shame that not all our vendors think so, but we’re getting there.

So, whenever possible we rely on the Security Team creating an AD Group and assigning individual logins to that group.  Membership is based upon a paper trail of approvals that currently runs about 3/8″ thick.  Welcome to the paperless age. 

That is what occurs outside, and without knowledge or input by the DBAs.  What happens next falls squarely on us.  We’re provided the AD Group name (not the individual members, mind you for we don’t care about them at all.)  We go about the process of creating a SQL Server login for the AD Group and assign all necessary rights to the individual databases, server roles, and objects that are authorized. 

That is it.  Users come and go, Security adds and removes membership to the AD Groups, and the DBA cares not one bit.

Except when we’re asked to troubleshoot a situation where a user is unable to connect to a database or a technician is requesting rights to a database.  Then we have two options:

  • Involve the Security Team and wait for them to find time in our schedule to tell us if so-and-so has rights to Domain\AD_Group
  • EXEC xp_logininfo

Very simply put, executing xp_logininfo will return a listing of all members of an AD Group if you pass in the parameters as follows. So long as that group is mapped to a login on the SQL Server instance you’re querying:

EXEC xp_logininfo ‘DOMAIN\AD_GROUP_NAME’, ‘members’

It really can not get much more simple than that.