Takeaway: Use “xp_logininfo” when curious about how and why someone has access to a database.
I had this I.M. conversation with a colleague at work yesterday.
Me: What was that server name again?
Stephen: It was SQL001
Me: Thanks I’m in.
Stephen: eyeballing security… I don’t see you on that list so if you do actually have access I’m confused
To be honest, I didn’t know why I had access either. Normally, to examine access, I look at security logins and look for users or groups that might match my own credentials. I do that here in SQL Server Management Studio’s Object Explorer:
The way I used to look at logins
In this case it’s not immediately obvious why I have access and what level of access I have.
Enter xp_logininfo
So I had this exchange with Allen Kinsel (@AllenKinsel) (via #sqlhelp on twitter):
xp_logininfo works beautifully for what I wanted to do.
It’s a command that’s too obscure. I think xp_loginfo should be more widely known (hence this blog post). And when I run:
exec xp_logininfo 'MYDOMAIN\mswart', 'all' |
I get:
| account name | type | privilege | mapped login name | permission path |
| MYDOMAIN\mswart | user | user | MYDOMAIN\mswart | MYDOMAIN\SupportGroup |
| MYDOMAIN\mswart | user | admin | MYDOMAIN\mswart | BUILTIN\Administrators |
This tells me exactly what kind of access I have and why. In this case, it looks like I have access for two reasons. First, I have access because I belong to the support group and the other reason is because I’m part of the “BUILTIN/Administrators” group. This is so much better than eyeballing a list of logins.
xp_logininfo: It’s worth remembering.
