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:
In this case it’s not immediately obvious why I have access and what level of access I have.
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'
|account name||type||privilege||mapped login name||permission path|
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.