How to query Active Directory with xp_logininfo

I use xp_logininfo when working with developers or Operations staff troubleshooting access permissions on a SQL Server database. It is very useful to identify the permission path an Active Directory (AD) based logon will use.

It isn’t uncommon for an AD account to be part of multiple AD groups. Those AD groups may be created as SQL Server Logons which have different privileges on different databases. Hopefully regular security audits will clear any anomalies

 To view permission path of individual AD logon

xp_logininfo 'my_domain\mylogon','all'

This leads to querying Active Directory from SQL Server using xp_logininfo. In many organisations SQL Server security policy, individual AD logons are not permitted as SQL Server Logons. Typically the ID must be included as part of an AD group. There are many practical reasons including making housekeeping much easier when an individual moves departments or leaves the organisation.

As a DBA, you’ll see a list of AD groups but no immediate visibility on members of the AD group. There are plenty of tools to query the AD including powershell AD tools. One immediate way is to use xp_logininfo .

To query AD global group members using xp_logininfo

xp_logininfo 'DOMAIN\group','members'

When I discovered this xp_loginfo I was pleased. Up until this point I’d ben using Powershell. Using Powershell Get-ADGroupMember gives you a ton of flexibility and access to a wider range of data, but xp_logininfo   has its use.

If you'd like to read on xp_logiinfo limitations read - Limitations of xp_logininfo

Read More on security

Powershell sql server security audit 

SQL Server – How to manage Failed Logon attempts