Limitations of xp_logininfo

20 December,2016 by Jack Vamvas

Question: I’d like to understand the limitations of xp_logininfo. I use xp_logininfo for quick reference to SQl Server security troubleshooting . The good thing about xp_logininfo is that it is part of SQL Server and as long as the permissions are in place – than it’s a handy tool.   No need to install initiate Powershell

I’ve noticed when I query using xp_logininfo to alternative trusted domains , but within the same farm , there is some unexpected results. I’d like to undertstand the limitations.

I’ve read the post How to query Active Directory with xp_logininfo

Answer: During my usage of xp_logininfo these are some of the limitations I've experienced. But like most tools, they are designed for a certain purpose , and if you have other requirements then it may be time to explore other approaches to querting Active Directory . For example : How to Export Active Directory Group Members with Powershell Get-ADGroupMember

>> xp_logininfo only returns details from AD global groups not universal groups. This can be confusing – but it’s important to understand – as it could save hours of troubleshooting. It could also influence the types of groups set up

>> The SQL service account should have sufficient rights to query Domain Controller. If the account cannot connect to Domain Controller, than no results are returned!

>> Using xp_logininfo with a Windows login which has different levels of access, such as sa and user – the sa level is reported. The way to resolve is by using the “all” keyword – which returns

>> Cannot enumerate through a group within a group. If xp_logininfo returns a set of members and one of the members is a group , than the nested group will have it’s members automatically enumerated. There are ways around this limitation using programmatic methods

Read More

Powershell sql server security audit (SQL Server DBA)

How to create a SQL Server Security Audit (SQL Server DBA)

 


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 Limitations of xp_logininfo


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