xp_logininfo
So, you are a typical SQL DBA and follow best practices, I mean Best Practices for security in your environment. These state that you should create Active Directory groups, then create SQL logins at the instance level for those groups on your SQL Servers. Rights to the individual databases and objects contained therein are handled by creating database users for those instance logins.
It is then by adding members to the AD groups, that rights are passed through to the individuals in your domain to the database. Let’s look at it as the following hierarchy (thanks Spell Checker!)
So, we do all this for a few reasons, but the important one is that it removes the DBA from the continual process of adding and removing logins and users from a SQL Server instance and places that responsibility on the team that is tasked with global security in the domain: the Security team.
However, I often find a stray security request hit the DBA desk at which point I need to provide educated direction to the necessary staff so they can get a domain user the rights that they require to meet their needs. Since the DBAs are removed from the day-to-day process of security the membership of these Active Directory groups hitting our SQL instances is a black box of sorts. However, there is a solution, granted that xp_cmdshell is enabled via the SQL Surface Area Configuration Tool, and you have the rights to execute xp_cmdshell.
xp_logininfo returns information about Windows logins as well as groups. By executing xp_logininfo and passing the parameter for the AD Group and requested output information such as what follows, you’re able to get a current listing of domain users who are members of the domain group.
EXEC xp_logininfo ‘DOMAIN\GROUP NAME’, ‘members’
If the specific security request is asking for rights for a database where an AD group already has identical rights to what is being requested, and the login for this individual is not a member of the AD group, I can pass along the information to the Security Team for group assignment. If the request is for multiple logins to have identical rights, I can direct the creation of an AD group and assign rights to a corresponding SQL Login and User once created.