Get SQL Server user permissions
I’ve found three ways of getting user permissions (grants and denies) in SQL Server:
- SQL Server Management Studio: It’s OK and user-friendly. But it’s not an option if you’ve a large farm of servers or you want to script it and create task to run the checks.
- Impersonate as the user and check its permissions.
- SELECT on system tables to see the permissions.
In this post I explain the last two options and the pros and cons they have.
For this example I use AdventureWorksDB that can be found at Codeplex.
Creating logins, users and giving permissions
To test the queries in both methods, I’ll create 2 logins and one user for each login. Then I’ll give different permissions to the users to test it.
USE AdventureWorks
-- Create login and user
CREATE LOGIN BobSmith WITH PASSWORD=N'myPa$$word', DEFAULT_DATABASE=AdventureWorks, CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
CREATE USER Bob FOR LOGIN BobSmith
-- Create login and user
CREATE LOGIN AliceSimpson WITH PASSWORD=N'myPa$$word', DEFAULT_DATABASE=AdventureWorks, CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
CREATE USER Alice FOR LOGIN AliceSimpson
EXEC sp_addrolemember N'db_datareader', N'Alice'
-- Give GRANTs for example
GRANT SELECT ON Production.Product TO Bob
GRANT SELECT, INSERT, UPDATE ON Production.ProductDescription TO Bob
DENY DELETE ON OBJECT::Production.ProductSubcategory TO Bob
GRANT EXECUTE TO Bob -- NOTE: This doesn't give execution rights to Table-valued functions!
DENY EXECUTE ON SCHEMA::HumanResources TO Bob
GRANT ALTER ON dbo.ufnLeadingZeros TO Bob
Impersonate as the user and check it’s permissions
The function fn_my_permissions is very useful when you want to know the currents user’s permissions.
Can be used for server, database or object basis. When used for objects, you must specify the object you are asking for in the first parameter.
Don’t appear the explicit DENY options. Sometimes that’s interesting.
I like to give this query to other IT people to let them know what they can do and to check the rights they have.
Pros:
- Shows effective rights. In our example, Bob has GRANT EXECUTE on all stored procedures and functions in all database, except the in ‘HumanResources’, so this query only shows the SP and functions it has rights on. And don’t show the DENY.
Cons:
- You need IMPERSONATE right on the user you want to check rights.
- In case you need to check all users, you should make an iteration and IMPERSONATE each user.
EXECUTE AS USER = 'Bob';
-- Server rights
SELECT * FROM fn_my_permissions(NULL, 'SERVER');
-- Database rights
SELECT * FROM fn_my_permissions(NULL, 'DATABASE');
-- Specific per object rigths
SELECT T.TABLE_TYPE AS OBJECT_TYPE, T.TABLE_SCHEMA AS [SCHEMA_NAME], T.TABLE_NAME AS [OBJECT_NAME], P.PERMISSION_NAME
FROM INFORMATION_SCHEMA.TABLES T
CROSS APPLY fn_my_permissions(T.TABLE_SCHEMA + '.' + T.TABLE_NAME, 'OBJECT') P
WHERE P.subentity_name = ''
UNION
SELECT R.ROUTINE_TYPE AS OBJECT_TYPE, R.ROUTINE_SCHEMA AS [SCHEMA_NAME], R.ROUTINE_NAME AS [OBJECT_NAME], P.PERMISSION_NAME
FROM INFORMATION_SCHEMA.ROUTINES R
CROSS APPLY fn_my_permissions(R.ROUTINE_SCHEMA + '.' + R.ROUTINE_NAME, 'OBJECT') P
ORDER BY OBJECT_TYPE, [SCHEMA_NAME], [OBJECT_NAME], P.PERMISSION_NAME
REVERT;
GO
You can also test it with ALICE and you will see that she has SELECT rights to TABLES, FUNCTIONS and VIEWS. All the SELECT permissions are due to db_datareader database permission.
SELECT on system tables to see the permissions
The following SELECT it’s very helpful when you want to get all explicitly granted or denied permissions in a database.
Pros:
- Shows GRANT permissions but also DENY permissions
- Don’t need IMPERSONATE grant to check it.
Cons:
- Explicit permissions due to database roles are not shown. You’ve to ‘calculate’ them
- To check if a user has privileges on an object you have to check the GRANT/DENY on a database/schema/object, and check database roles. So it’s not straightforward…
SELECT DP.class_desc AS object_type, GR.type_desc AS grantor_user_type, GR.name AS grantor, GE.type_desc AS grantee_user_type, GE.name AS grantee,
DP.permission_name, state_desc,
CASE
WHEN S.name IS NOT NULL THEN S.name
ELSE ISNULL(OBJECT_SCHEMA_NAME(DP.major_id), 'all_database')
END AS [schema_name],
CASE
WHEN S.name IS NOT NULL THEN NULL
ELSE OBJECT_NAME(DP.major_id)
END AS [table_name]
FROM sys.database_permissions DP
INNER JOIN sys.database_principals GR ON GR.principal_id = DP.grantor_principal_id
INNER JOIN sys.database_principals GE ON GE.principal_id = DP.grantee_principal_id
LEFT JOIN sys.schemas AS S ON S.schema_id = DP.major_id
WHERE NOT (ISNULL(OBJECT_SCHEMA_NAME(DP.major_id), 'all_database') = 'sys'
AND DP.class_desc = 'OBJECT_OR_COLUMN')
References
Some queries are based on the script Retrieve Database Object permission scripts and Role members scripts written by Gokhan Varol.
Source code
The source code of this example can be found here.
