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.
I like using sys.user_token and sys.login_token (depending on what level I’m trying to figure out the perms for). Of course, as a sysadmin, I usually have IMPERSONATE rights for whomever so I do something like:
execute as login=’mydomain\some.user’
select * from sys.user_token
revert
Comment by Ben Thul — October 30, 2013 @ 11:04 am
Those are cool Ben. I haven’t used those before. I’m going to have to ask my DBA friends what they like to use.
One of the reasons I think I like xp_logininfo is that I like the “permission path” field that is output by xp_logininfo. It gives a quick way to tell what groups users belong to (or if they belong to any).
Comment by Michael J. Swart — October 30, 2013 @ 11:11 am
I LOVE me some xp_logininfo. So much that my very first SQL Server presentation was built around it. I have two scripts that could be helpful to you down the road that I put together leveraging it in case you want to make use of them down the road:
http://www.mikefal.net/scripts/server_role_audit.sql
http://www.mikefal.net/scripts/db_roles_audit.sql
My only “complaint” is it doesn’t seem to handle nested AD groups. Not that this is a common practice, but a gotcha to watch out for.
Comment by Mike Fal — October 30, 2013 @ 1:05 pm
Thanks Mike! Those are awesome and I wish I had known about them earlier.
Comment by Michael J. Swart — October 30, 2013 @ 1:12 pm
[…] You’ve got access, but I’m not sure why – Michael J. Swart (Blog|Twitter) […]
Pingback by (SFTW) SQL Server Links 01/11/13 • John Sansom — November 1, 2013 @ 8:27 am
Looks good – at least on those servers it works on.
We have others where it fails because of collation differences
Msg 468, Level 16, State 9, Procedure xp_logininfo, Line 90
Cannot resolve the collation conflict between “SQL_Latin1_General_CP1_CI_AS” and “Latin1_General_CI_AS” in the equal to operation.
Comment by Craig — November 4, 2013 @ 4:17 am
That’s interesting Craig. It sounds as if you’ve come across the bug described here:
http://serverfault.com/questions/16872/xp-logininfo-returns-collation-error
Could you check out the comments there and see if they help at all? I think calling master.sys.xp_logininfo instead of xp_logininfo might be promising (if you really really need this to work).
What’s interesting is that the error gives a line number. We can look at the definition of xp_logininfo and see what it’s doing.
It’s comparing the parameters that are passed in without regard to collations. But even knowing that, we don’t have an opportunity to modify the definition. I hope prefixing the procedure call with “master.sys.” helps. Report back and let us know.
Comment by Michael J. Swart — November 4, 2013 @ 9:04 am
If you leverage Active Directory group security, you can also use xp_logininfo to give you a listing of the members of the group…… exec xp_logininfo ‘Domain\GroupName’,’members’
Comment by Dre Alicea — November 4, 2013 @ 9:17 am
Yes prefixing it with master.sys does indeed allow it to work.
I suspect the server may have been set up with the wrong collation but don’t have time to check at present. I’ve seen similar before with temp tables where tempdb is set up with the server default but is different to the application database.
Comment by Craig — November 4, 2013 @ 9:29 am
Thanks for this article. I used the usergroup parameter and found that a non-developer had gotten inserted into a developer user group on my team’s server. We are looking into why she got the access now and will probably be removing her from the group and adding her to a read only group. I never would have noticed that without this article, so thanks again.
Comment by Stuart — November 12, 2013 @ 9:33 am
That’s great to hear Stuart. I’m here to serve.
Comment by Michael J. Swart — November 12, 2013 @ 9:37 am
Reading your blogs, I always learn something new and interesting, even if it is a topic I have had some exposure to. Thanks for all of you contributions to the community… and thanks for the people that have added the comments… I learn from you too!
Cheers,
Scott
Comment by Scott Stauffer — November 22, 2013 @ 1:08 pm
Thanks Scott! Feedback like that is why I help.
Comment by Michael J. Swart — November 22, 2013 @ 1:13 pm
[…] blogged about Michael J Swart (https://michaeljswart.com/2013/10/youve-got-access-but-im-not-sure-why/), after reading Michaels blog this is the first thing I use to find out what permissions people […]
Pingback by Troubleshoot User Logins | SQL Notes From The Underground — December 10, 2013 @ 10:09 am