sqlsurfer101 (8/19/2013)
When a valid AD account doesn't have access to the instance, I think you get no results, but when the AD account doesn't exist is when you generally get the error 'could not obtain information..' So perhaps I don't know. Could it be that you typed in the name and there is a typo?
No, I checked it very carefully, and retyped it several times, just in case there might have been a hidden character lurking in there. The name absolutely is correct, but the error persists.
"Are you telling me that this SP has known incorrect behavior?" - Maybe not anymore. I did not know about the 'all' parameter. I just tried it. It seems to provide information I need. I had only used xp_logininfo 'domain\user' - that I thought was returning inconsistent info. (We have other custom built means of getting permissions information, so I haven't needed/missed xp_logininfo)
How do you do it? Is it something I might be able to use?
EXEC xp_logininfo 'nmp\username', 'members' -> this needs to be EXEC xp_logininfo 'nmp\groupname', 'members'
Oops, thank you, good catch. But I just tried it like this:
exec xp_logininfo 'nmp\paleoces', 'members'and the two users in this group came up, like so:
NMP\kvacekji user user NMP\kvacekji nmp\paleoces
NMP\zagorsekka user user NMP\zagorsekka nmp\paleoces
I then took the two users again, using copy/paste, just to be certain that I didn't mistype something, and the results are the same:
exec xp_logininfo 'NMP\zagorsekka'
and
exec xp_logininfo 'NMP\zagorsekka', 'all'both throw the error, but exec xp_logininfo 'NMP\kvacekji'
and
exec xp_logininfo 'NMP\kvacekji', 'all'both work correctly. And both users can log in to the database.
All in all, for sanity, my first steps would be to check if there was a typo in the name, and if it is not a typo, to check if the user can access the database even though you mentioned his login is mapped to the database.
Well, the name IS correct, and the user CAN log in to the database. Can you think of anything else I might try?