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:

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:

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