Find Active Directory Group Members in SQL Server

Active Directory Group Members in SQL

SQL Server is brilliant.

It’s a pleasure to work with, sometimes the queries and structures required are complex and a challenge to implement, but ultimately you can rely on the SQL engine doing it’s job, so you can focus on yours.

This isn’t always the case. Take Visual Studio for example. The environmental issues we have hit in recent development have cost us more time than any poor architectural decisions we have stumbled into. You just don’t get that when using the SQL Server engine, or perhaps I just know more about the SQL Engine than I do Visual Studio.

Recently, someone approached me whilst I was constructing some queries within SSMS, and asked me to check if they were a member of an active directory group that had permissions to a certain folder held on the shared drive. At the time I was sat thinking to myself, I’d love to be a full blown DBA, SQL Server is great, and I enjoy working with it, and so the thought crossed my mind when I was asked the question;

Can I Find Active Directory Group Members in SQL?

So, on with a little hunt I went, and I came across the xp_logininfo transact SQL statement, which will return information about Window’s User’s and Groups.

 EXEC master.dbo.xp_logininfo 'DomainName\AccountName'

Executing this command will show information regarding the user entered, including the object type, group membership and the login name the account maps to.

Experimenting a little, I discovered you can execute this same command against a Group object to view the members of the Group in question;

<EXEC xp_logininfo 'Domain\GroupName'

And with a whizz and a bang, I can view the members of the domain group.

It truly is easy to find active directory group members in SQL Server.

About Adam Riddick

When not Developing, Adam moonlights as a climbing frame for his two children. Adam Riddick was founded in 1987, in County Durham, England. Read More ...

31. October 2014 by Adam Riddick
Categories: Databases, SQL, Tips | Leave a comment

Inspire me with your wisdom ...