Script to determine permissions in SQL Server 2005

ProblemAt times I run into application issues when I am unable to perform particular functions in the application that some of my peers are able to complete. I need to be able to quickly and easily understand my permissions in SQL Server. Do you know of an easy way to do so? Do you know how I can compare 2 different users to see what the difference could be at an instance, database or object level? Any and all suggestions would be appreciated.

SolutionAlthough a few different options (Management Studio, system stored procedures, system views, custom scripts, etc.) exist to determine your permissions in SQL Server, in this tip we want to outline the functionality from the fn_my_permissions table valued function. In a nutshell, the fn_my_permissions table valued function accepts the name of the object and the object type then returns the effective permissions based on permissions granted directly to the user\login, granted to a group the user\login is a member of or implied higher level permissions as well as permissions not denied at any of these levels. From an object level, below outlines the types of objects information can be returned about:

APPLICATION ROLE

ASSEMBLY

ASYMMETRIC KEY

CERTIFICATE

CONTRACT

DATABASE

ENDPOINT

FULLTEXT CATALOG

LOGIN

MESSAGE TYPE

OBJECT

REMOTE SERVICE BINDING

ROLE

ROUTE

SCHEMA

SERVER

SERVICE

SYMMETRIC KEY

TYPE

USER

XML SCHEMA COLLECTION

As DBAs with administrative privileges, this function might not be very interesting due to our level of privileges. However, it is possible to impersonate a user and then determine the effective rights they have been granted to troubleshoot a permissions issue. If one user is able to perform a task and another is not, then this tabled value function can be extremely valuable to determine differences in object level access.

Let's take a look at a few basic examples when using the fn_my_permissions table valued function based on your rights:

Example - SQL Server Instance Rights

USE AdventureWorks;SELECT*FROM fn_my_permissions(NULL,'SERVER');GO

Example - Database Rights

USE AdventureWorks;SELECT*FROM fn_my_permissions('AdventureWorks','DATABASE');GO

The first three examples are basic and straight forward to determine rights based on your credentials, but do not really showcase the value of the fn_my_permissions table valued function. Let's create four different logins and users then grant and deny rights. Once this is finished, let's issue fn_my_permissions for the specific objects to determine the effective rights for each of the users.

use [AdventureWorks]GOGRANTEXECUTEON [dbo].[uspGetBillOfMaterials] TO [Tom]GOGRANTEXECUTEON [dbo].[uspGetEmployeeManagers] TO [Tom]GOGRANTEXECUTEON [dbo].[uspGetManagerEmployees] TO [Tom]GOGRANTEXECUTEON [dbo].[uspGetWhereUsedProductID] TO [Tom]GOGRANTEXECUTEON [dbo].[uspLogError] TO [Tom]GO

use

[AdventureWorks]GOGRANTSELECTON [HumanResources].[EmployeeAddress] TO [Linda]GOGRANTEXECUTEON [dbo].[uspGetBillOfMaterials] TO [Linda]GOGRANTSELECTON [dbo].[MyTableVar] TO [Linda]GOGRANTSELECTON [HumanResources].[Department] TO [Linda]GOGRANTSELECTON [dbo].[AWBuildVersion] TO [Linda]GOGRANTSELECTON [HumanResources].[EmployeeDepartmentHistory] TO [Linda]GOGRANTSELECTON [dbo].[DatabaseLog] TO [Linda]GOGRANTSELECTON [dbo].[ErrorLog] TO [Linda]GOGRANTSELECTON [HumanResources].[JobCandidate] TO [Linda]GOGRANTSELECTON [HumanResources].[Shift] TO [Linda]GOGRANTSELECTON [HumanResources].[Employee] TO [Linda]GOGRANTSELECTON [HumanResources].[EmployeePayHistory] TO [Linda]GO

Deny Rights

use [AdventureWorks]GODENYEXECUTEON [dbo].[uspGetEmployeeManagers] TO [Linda]GO

use

[AdventureWorks]GODENYDELETEON [HumanResources].[JobCandidate] TO [Tom]GODENYINSERTON [HumanResources].[JobCandidate] TO [Tom]GODENYSELECTON [HumanResources].[JobCandidate] TO [Tom]GODENYDELETEON [HumanResources].[EmployeePayHistory] TO [Tom]GODENYINSERTON [HumanResources].[EmployeePayHistory] TO [Tom]GODENYSELECTON [HumanResources].[EmployeePayHistory] TO [Tom]GO

use

[AdventureWorks]GODENYALTERON [HumanResources].[EmployeePayHistory] TO [Jessica]GODENY CONTROL ON [HumanResources].[EmployeePayHistory] TO [Jessica]GODENYDELETEON [HumanResources].[EmployeePayHistory] TO [Jessica]GODENYINSERTON [HumanResources].[EmployeePayHistory] TO [Jessica]GODENYREFERENCESON [HumanResources].[EmployeePayHistory] TO [Jessica]GODENYSELECTON [HumanResources].[EmployeePayHistory] TO [Jessica]GODENY TAKE OWNERSHIP ON [HumanResources].[EmployeePayHistory] TO [Jessica]GODENYUPDATEON [HumanResources].[EmployeePayHistory] TO [Jessica]GODENYVIEW DEFINITION ON [HumanResources].[EmployeePayHistory] TO [Jessica]GO

AdventureWorks;EXECUTEASUSER='Jessica';SELECT*FROM fn_my_permissions('HumanResources.Shift','Object')ORDERBY subentity_name, permission_name ;REVERT;GO-- For this object, Jessica has all rights possible

USE AdventureWorks;EXECUTEASUSER='Jessica';SELECT*FROM fn_my_permissions('[HumanResources].[EmployeePayHistory]','Object')ORDERBY subentity_name, permission_name ;REVERT;GO-- For this object, Jessica has no rights

USE AdventureWorks;EXECUTEASUSER='Tom';SELECT*FROM fn_my_permissions(NULL,'Database')ORDERBY subentity_name, permission_name ;REVERT;GO-- Tom is only able to connect to the database, but has no other rights

USE

AdventureWorks;EXECUTEASUSER='Tom';SELECT*FROM fn_my_permissions('dbo.uspGetBillOfMaterials','Object')ORDERBY subentity_name, permission_name ;REVERT;GO-- Tom is only able to execute the stored procedure

Although a few different options exist for determining the effective permissions for a user or an object, the fn_my_permissions table valued function is a simple solution which can be used as a means to compare two different users in a manual manner or used as a building block for more complex scripts to compare overall rights.

Keep in mind that users are able to query the fn_my_permissions table valued function, so this could be one tool you can share with your power users so they are able to troubleshoot basic issues and be able to communicate effectively when they run into a suspected security issue.

This can be done even easier.
A new version of scriptlogic's enterprise secuirty reporter 3.6 (beta version is available at http://wwww.scriptlogic.com/beta) is a good answer to sql server security reporting needs.
The solution includes a very powerful discovering and reporting engine with built-in ready-made and custom reports including reports on database object and schema permissions, effective database object permissions, database users and role membership, server logins and server role membership.
The tool supports all popular SQL Server versions like SQL Server 2000 and 2005 as well as MSDE and SQL 2005 Express.