Script to determine permissions in SQL Server 2005

By: Jeremy Kadlec | 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.

About the author

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

Script to determine permissions in SQL Server 2005

Problem

Solution

Next Steps

About the author

Comments For This Article