There’s numerous ways to resolve troubleshooting issues, below are some of key methods I use:

 

XP_LOGININFO

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:

IE

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' 

http://technet.microsoft.com/en-us/library/ms190369.aspx

 

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.

sys.login_token

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)

http://technet.microsoft.com/en-us/library/ms186740.aspx

EXECUTE AS LOGIN = 'domain\user'
GO
SELECT *
FROM sys.login_token
REVERT

sys.user_login

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

http://technet.microsoft.com/en-us/library/ms188421.aspx

 

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

http://stackoverflow.com/questions/3361778/get-list-of-all-database-users-with-specified-role

/*
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.

http://consultingblogs.emc.com/jamiethomson/archive/2007/02/09/SQL-Server-2005_3A00_-View-all-permissions–_2800_2_2900_.aspx

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:

sp_srvpermissions
http://sqlstudies.com/free-scripts/sp_srvpermissions/

sp_dbpermissions
http://sqlstudies.com/free-scripts/sp_dbpermissions/

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)
http://www.brentozar.com/archive/2011/07/why-does-hurt-when-i-audit/

 

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
http://msdn.microsoft.com/en-us/library/ms178542.aspx

Database- Level Views

sys.database_permissions
sys.database_role_members
sys.database_principals
sys.master_key_passwords

Server – Level Views

sys.server_permissions
sys.sql_logins
sys.server_principals
sys.system_components_surface_area_configuration
sys.server_role_members

Advertisements