Limitations of xp_logininfo

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)