Wednesday, December 12, 2018

Check AD members and groups

All of the companies I have worked for have individual AD accounts with permissions in SQL. They have all wanted to keep things easier by using AD security groups, but there are various reasons why that isnt always the reality, such as: software limitations, linked server config, legacy needs, changes impacting critical processes, etc..

I was recently introduced to the xp_logininfo stored procedure. It is a very handy tool to use to gather information about an AD account, or for user groups. Whenever someone asks for access, it is my go-to to find all of the groups they belong to which already have some permissions.  I still check AD to see if there are more appropriate groups (based on who else is in a group), but I thought I would share how this stored proc is helpful.

  • xp_logininfo 'ADaccountName', 'all'
    By including the AD account name, you can get a list of all of the AD security groups that user belongs to, which have permissions on that particular instance being queried. That last part is important. It isnt a list of all of the network groups that user belongs to - it is only those that already exist on that instance of sql. For example, if my account belongs to GroupA, GroupB and GroupC; the sql instance I am querying already has GroupB with permissions; then that will be the only result returned even though my account belongs to all 3 groups in AD.
  • xp_logininfo 'ADsecurityGroupName', 'members'
    By including the AD security group name, you can get a list of all of the AD members who belong in that group. This is an easy way of querying to get a list of everyone who has access.

 

Archives

Powered by mod LCA