How to query Active Directory with xp_logininfo

13 January,2016 by Jack Vamvas

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


Author: Jack Vamvas (http://www.sqlserver-dba.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on How to query Active Directory with xp_logininfo


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer