Michael J. Swart

October 30, 2013

“You’ve got access, but I’m not sure why”

Filed under: Miscelleaneous SQL,SQLServerPedia Syndication,Technical Articles — Michael J. Swart @ 9:00 am

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:

Looking At Logins

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):

Allen Kinsel saves the day

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.

Powered by WordPress