Troubleshoot User Logins
There’s numerous ways to resolve troubleshooting issues, below are some of key methods I use:
Recently blogged about Michael J Swart (http://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 have. It can except either a Group or login account and lists out what groups they are in:
If you want to know what Groups an account is part of:
EXEC xp_logininfo 'domain\user', 'all'
Note always use the . ‘all’ on user, you don’t have to, but if you don’t it will just return one group that the login is member of
If you want to know whos in a Group:
EXEC xp_logininfo 'domain\SQL Team', 'members'
EXECUTE_AS with Sys.Login_token / sys.user_token
I saw this used originally by Joe Sack in a lighting talk one night. Using EXECUTE AS you change your user context to the user your troubleshooting (http://msdn.microsoft.com/en-us/library/ms181362.aspx ), then using sys.login_toke and sys.user_token you can troubleshoot what permissions they have.
returns ALL the Active Directory Groups that they are a member off. (could be overkill for what you need, could highlight something else you wouldn’t see using xp_logininfo)
EXECUTE AS LOGIN = 'domain\user' GO SELECT * FROM sys.login_token REVERT
returns information about the user account against the database your querying. this gives you more detail than xp_logininfo, as it will show you what roles the account is connected to:
EXECUTE AS LOGIN = 'domain\user' GO USE myDatabase GO SELECT * FROM sys.user_token REVERT
3rd Party Queries for Permission Troubleshooting
These are a collection of queries that I’ve either found or made to get deeper details of permissions.
StackOverflow (Jim V)
List out any members of fixed roles
/* List all Users with fixed role permissions (run against DB of interest) */ SELECT p.name AS database_role , dp.name AS database_user FROM sys.database_role_members m JOIN sys.database_principals p ON m.role_principal_id = p.principal_id JOIN sys.database_principals dp ON m.member_principal_id = dp.principal_id;
Jamie Thomson – View All Permissions
View all permissions (Get a break down on all the permissions of the roles.
Kenneth Fisher AKA http://sqlstudies.com Stored Procedures
Another freebie from the community (big thank you!) , Kenneth has created 2 very nice Stored Procedures that dig into Server level permissions and database level permissions:
BrentOzar Unlimited post – Why does it hurt when I audit
Collection of Queries to see what the state the instance is in. (find orphan users, enabled Guest accounts etc)
Tables to look at for creating a custom query:
These are the tables that have more details about permissions
Views to build Queries on Login / User Permissions
Database- Level Views
Server – Level Views