Script to determine permissions in SQL Server 2005

Problem

At 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.

Solution

Although 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 AdventureWorksSELECT *
FROM fn_my_permissions(NULL, ‘SERVER’
GO
 

Example – Database Rights

USE AdventureWorksSELECT *
FROM fn_my_permissions(‘AdventureWorks‘, ‘DATABASE’
GO
 

Example – Table Rights

USE AdventureWorksSELECT *
FROM fn_my_permissions(‘HumanResources.Employee‘, ‘OBJECT’)
ORDER BY subentity_name, permission_name
GO
 

Security Example

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.
























Create SQL Server Logins and Users

USE [master]
GO
CREATE LOGIN [Tom] WITH PASSWORD=N‘Tom‘, DEFAULT_DATABASE=[AdventureWorks], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO

USE [AdventureWorks]
GO
CREATE USER [Tom] FOR LOGIN [Tom]
GO


USE [AdventureWorks]
GO
ALTER USER [Tom] WITH DEFAULT_SCHEMA=[HumanResources]
GO
 


USE [master]
GO
CREATE LOGIN [Linda] WITH PASSWORD=N‘Linda’, DEFAULT_DATABASE=[AdventureWorks], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO


USE [AdventureWorks]
GO
CREATE USER [Linda] FOR LOGIN [Linda]
GO


USE [AdventureWorks]
GO
ALTER USER [Linda] WITH DEFAULT_SCHEMA=[Purchasing]
GO


USE [master]
GO
CREATE LOGIN [Jessica] WITH PASSWORD=N‘Jessica‘, DEFAULT_DATABASE=[AdventureWorks], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO


USE [AdventureWorks]
GO
CREATE USER [Jessica] FOR LOGIN [Jessica]
GO


USE [AdventureWorks]
GO
ALTER USER [Jessica] WITH DEFAULT_SCHEMA=[Sales]
GO


USE [master]
GO
CREATE LOGIN [Katie] WITH PASSWORD=N‘Katie‘, DEFAULT_DATABASE=[AdventureWorks], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO


USE [AdventureWorks]
GO
CREATE USER [Katie] FOR LOGIN [Katie]
GO


USE [AdventureWorks]
GO
ALTER USER [Katie] WITH DEFAULT_SCHEMA=[dbo]
GO
 


Grant Rights to Fixed Database Roles

USE [AdventureWorks]
GO
EXEC sp_addrolemember N‘db_owner‘, N‘Jessica‘
GO

EXEC sp_addrolemember N‘db_datareader‘, N‘Katie‘
GO


EXEC sp_addrolemember N‘db_datawriter‘, N‘Katie‘
GO
 


Grant Object Level Rights

use [AdventureWorks]
GO
GRANT EXECUTE ON [dbo].[uspGetBillOfMaterials] TO [Tom]
GO
GRANT EXECUTE ON [dbo].[uspGetEmployeeManagers] TO [Tom]
GO
GRANT EXECUTE ON [dbo].[uspGetManagerEmployees] TO [Tom]
GO
GRANT EXECUTE ON [dbo].[uspGetWhereUsedProductID] TO [Tom]
GO
GRANT EXECUTE ON [dbo].[uspLogError] TO [Tom]
GO

use [AdventureWorks]
GO
GRANT SELECT ON [HumanResources].[EmployeeAddress] TO [Linda]
GO
GRANT EXECUTE ON [dbo].[uspGetBillOfMaterials] TO [Linda]
GO
GRANT SELECT ON [dbo].[MyTableVar] TO [Linda]
GO
GRANT SELECT ON [HumanResources].[Department] TO [Linda]
GO
GRANT SELECT ON [dbo].[AWBuildVersion] TO [Linda]
GO
GRANT SELECT ON [HumanResources].[EmployeeDepartmentHistory] TO [Linda]
GO
GRANT SELECT ON [dbo].[DatabaseLog] TO [Linda]
GO
GRANT SELECT ON [dbo].[ErrorLog] TO [Linda]
GO
GRANT SELECT ON [HumanResources].[JobCandidate] TO [Linda]
GO
GRANT SELECT ON [HumanResources].[Shift] TO [Linda]
GO
GRANT SELECT ON [HumanResources].[Employee] TO [Linda]
GO
GRANT SELECT ON [HumanResources].[EmployeePayHistory] TO [Linda]
GO


 

Deny Rights
use [AdventureWorks]
GO
DENY EXECUTE ON [dbo].[uspGetEmployeeManagers] TO [Linda]
GO

use [AdventureWorks]
GO
DENY DELETE ON [HumanResources].[JobCandidate] TO [Tom]
GO
DENY INSERT ON [HumanResources].[JobCandidate] TO [Tom]
GO
DENY SELECT ON [HumanResources].[JobCandidate] TO [Tom]
GO
DENY DELETE ON [HumanResources].[EmployeePayHistory] TO [Tom]
GO
DENY INSERT ON [HumanResources].[EmployeePayHistory] TO [Tom]
GO
DENY SELECT ON [HumanResources].[EmployeePayHistory] TO [Tom]
GO


use [AdventureWorks]
GO
DENY ALTER ON [HumanResources].[EmployeePayHistory] TO [Jessica]
GO
DENY CONTROL ON [HumanResources].[EmployeePayHistory] TO [Jessica]
GO
DENY DELETE ON [HumanResources].[EmployeePayHistory] TO [Jessica]
GO
DENY INSERT ON [HumanResources].[EmployeePayHistory] TO [Jessica]
GO
DENY REFERENCES ON [HumanResources].[EmployeePayHistory] TO [Jessica]
GO
DENY SELECT ON [HumanResources].[EmployeePayHistory] TO [Jessica]
GO
DENY TAKE OWNERSHIP ON [HumanResources].[EmployeePayHistory] TO [Jessica]
GO
DENY UPDATE ON [HumanResources].[EmployeePayHistory] TO [Jessica]
GO
DENY VIEW DEFINITION ON [HumanResources].[EmployeePayHistory] TO [Jessica]
GO
 

Examples to determine final privileges for particular objects
USE AdventureWorksEXECUTE AS USER = ‘Jessica’SELECT *
FROM fn_my_permissions(NULL, ‘Database’)
ORDER BY subentity_name, permission_name
REVERTGO
— At a database level Jessica has administrative rights

USE AdventureWorksEXECUTE AS USER = ‘Jessica’SELECT *
FROM fn_my_permissions(‘HumanResources.Shift’, ‘Object’)
ORDER BY subentity_name, permission_name
REVERTGO
— For this object, Jessica has all rights possible


USE AdventureWorksEXECUTE AS USER = ‘Jessica’SELECT *
FROM fn_my_permissions(‘[HumanResources].[EmployeePayHistory]’, ‘Object’)
ORDER BY subentity_name, permission_name
REVERTGO
— For this object, Jessica has no rights


USE AdventureWorksEXECUTE AS USER = ‘Tom’SELECT *
FROM fn_my_permissions(NULL, ‘Database’)
ORDER BY subentity_name, permission_name
REVERTGO
— Tom is only able to connect to the database, but has no other rights


USE AdventureWorksEXECUTE AS USER = ‘Tom’SELECT *
FROM fn_my_permissions(‘dbo.uspGetBillOfMaterials‘, ‘Object’)
ORDER BY subentity_name, permission_name
REVERTGO
— Tom is only able to execute the stored procedure


USE AdventureWorksEXECUTE AS USER = ‘Katie’SELECT *
FROM fn_my_permissions(NULL, ‘Database’)
ORDER BY subentity_name, permission_name
REVERTGO
— Katie is able to perform SELECT, INSERT, UPDATE and DELETE commands


USE AdventureWorksEXECUTE AS USER = ‘Linda’SELECT *
FROM fn_my_permissions(‘Linda‘, ‘User’)
ORDER BY subentity_name, permission_name
REVERTGO
— Linda’s user permissions
 

Next Steps

  • 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.
  • For more information about fn_my_permissions review the information in SQL Server 2005 Books online.
  • Check out these security related tips:
  • The next time you are faced with a SQL Server permissions related question, consider the fn_my_permissions as a means to research the overall issue.

Related Posts

Leave a Reply

Your email address will not be published. Required fields are marked *