xp_logininfo gives error while trying to fetch group info

Question

xp_logininfo gives error while trying to fetch group info

Maverick100877

SSCertifiable

Points: 5240
More actions
May 3, 2016 at 7:35 am

#326783

Hi All,
We have a requirement to pull the members of groups that have access to SQL server and check for their login details for audit purpose.We are using xp_logininfo for that but we are gettign the following error
Msg 15404, Level 16, State 4, Procedure xp_logininfo, Line 42
Could not obtain information about Windows NT group/user 'domain\groupname', error code 0x2147.
Also is there any other way of pulling this information apart from xp_logininfo.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323504 More actions · Answer 1

some groups cannot be enumerated , I've seen that on my AD, where we have two forests of AD accounts with two way trusts.

I've found we've got a groups with mixed users from two forests, and also some groups in the other forest, and i cannot enumerate members with xp_loginginfo.

an alternative is to do it with powershell and the Get-ADGroupMember commandlet.

I posted an article recently on enumerating via xp_logininfo, that uses a try-catch to identify who we could and could not enumerate: at least that runs without an error, and shows which groups i cannot enumerate as a list for digging deeper.

http://www.sqlservercentral.com/articles/Active+Directory/138308/

I also recently posted how to enumerate users and groups via Powershell:

http://www.sqlservercentral.com/articles/powershell/137871/

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Yasir Anis SSCommitted Points: 1861 More actions · Answer 2

Thanks @lowell.

What's the reason for this that we can't enumerate groups from same AD ?

We have domain MYAD and two forests MYAD1 and MYAD2.

For those AD groups which have members from MYAD and MYAD2 , xp_logininfo is working perfectly.

For those AD groups which have members from MYAD and MYAD1, xp_logininfo is giving this error “A global group cannot have a cross-domain member.” but the group scope is universal not Global.

Jeff Moden SSC Guru Points: 1003865 More actions · Answer 3

If you look at the original 5 year old post on this thread, the OP didn't include the actual command they used. It could have been a spelling error in the domain name, group name, or user account. Another reason (which I believe is the case in this case) is that xp_logininfo doesn't work for cross-domain lookups.

Also, it only works for Active Directory global groups and will not work for "universal groups".

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)