Script to determine permissions in SQL Server 2005

By:   |   Updated: 2008-02-22   |   Comments (3)   |   Related: > Security


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 AdventureWorks;
SELECT *
FROM fn_my_permissions(NULL, 'SERVER');
GO
 

Example - Database Rights

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

Example - Table Rights

USE AdventureWorks;
SELECT *
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 AdventureWorks;
EXECUTE AS USER = 'Jessica';
SELECT *
FROM fn_my_permissions(NULL, 'Database')
ORDER BY subentity_name, permission_name ;
REVERT;
GO
-- At a database level Jessica has administrative rights

USE AdventureWorks;
EXECUTE AS USER = 'Jessica';
SELECT *
FROM fn_my_permissions('HumanResources.Shift', 'Object')
ORDER BY subentity_name, permission_name ;
REVERT;
GO
-- For this object, Jessica has all rights possible

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

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

USE AdventureWorks;
EXECUTE AS USER = 'Tom';
SELECT *
FROM fn_my_permissions('dbo.uspGetBillOfMaterials', 'Object')
ORDER BY subentity_name, permission_name ;
REVERT;
GO
-- Tom is only able to execute the stored procedure

USE AdventureWorks;
EXECUTE AS USER = 'Katie';
SELECT *
FROM fn_my_permissions(NULL, 'Database')
ORDER BY subentity_name, permission_name ;
REVERT;
GO
-- Katie is able to perform SELECT, INSERT, UPDATE and DELETE commands

USE AdventureWorks;
EXECUTE AS USER = 'Linda';
SELECT *
FROM fn_my_permissions('Linda', 'User')
ORDER BY subentity_name, permission_name ;
REVERT;
GO
-- 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.


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Jeremy Kadlec Jeremy Kadlec is a Co-Founder, Editor and Author at MSSQLTips.com with more than 300 contributions. He is also the CTO @ Edgewood Solutions and a six-time SQL Server MVP. Jeremy brings 20+ years of SQL Server DBA and Developer experience to the community after earning a bachelor's degree from SSU and master's from UMBC.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Article Last Updated: 2008-02-22

Comments For This Article




Wednesday, February 8, 2012 - 8:47:49 PM - Prakash Heda Back To Top (15944)

Nice one....useful

 

Prakash

www.sqlfeatures.com

 

 


Friday, April 11, 2008 - 10:11:24 AM - admin Back To Top (865)

Mark,

Thanks for letting us know about the product.

Thank you,
The MSSQLTips.com Team


Monday, April 7, 2008 - 8:04:23 AM - Mark Smithson Back To Top (836)
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.














get free sql tips
agree to terms