Finding and Troubleshooting SQL Server Permissions

It can be very tricky to troubleshoot user permissions on SQL Server. As SQL Server DBA, we are quite often questioned by users about what permissions they’ve got.  The permissions can be granted on server, database, schema and database object level (such as table, store proc, views, functions, and etc). To avoid dig through many screens or levels in management studio, fn_my_permission, as a built-in table value function from SQL Server 2005, provides the list of all the permissions effectively granted to the principle on a securable.

Before starting the permission checks, your login needs to be an account that has sysadmin role.

To list all permissions on server:

If login isn't specified, it shows what permissions the caller has by default.

                                                                       

EXECUTE AS LOGIN = 'Domain\UserLogin'

SELECT * FROM fn_my_permissions(NULL, NULL)

REVERT

Revert is to switche the execution context back to the caller of the last EXECUTE AS statement

Or alternative use

SELECT * FROM fn_my_permissions(NULL, 'SERVER')

To list all permissions on database:

If database name isn't specified, NULL uses current database by default    

                                                      

EXECUTE AS LOGIN = 'Domain\UserLogin'                              

SELECT * FROM fn_my_permissions (NULL, 'DATABASE')                

REVERT                                                             

To list all permissions on Schema:

Here using ‘dbo’ schema as a sample

EXECUTE AS LOGIN = 'Domain\UserLogin'

SELECT * FROM fn_my_permissions ('dbo', 'SCHEMA')

REVERT

To list all the permissions on the table in current database:

EXECUTE AS LOGIN = 'Domain\UserLogin'

SELECT * FROM fn_my_permissions ('dbo.mytablename', 'OBJECT')

REVERT

The object can also be store proc, function, views and application role, etc on database level.

To list all the permissions of what the given user has on current database:

SELECT * FROM fn_my_permissions('Domain\UserLogin', 'USER')

The same applies for the parameter of LOGIN. If you want to find out whether you can impersonate someone, this script will tell.