Issues Determining an Individual SQL Server User's Permissions

By:   |   Updated: 2012-07-25   |   Comments (4)   |   Related: More > Security


Recently I was supporting a third party application. It queries to determine what tables it has permissions to before it proceeds with the rest of its functionality. We had implemented permissions based on the best practice of creating roles, assigning the permissions to the roles, and then making the users members of the roles. The application was querying INFORMATION_SCHEMA.TABLE_PRIVILEGES and of course didn't find any permissions directly against the user in question. We ended up granting explicit permissions to the user so the application would work, but I'm more interested in the general case. How can I determine permissions for an individual user?


Let's take the simplest case, where permissions are assigned directly against objects such as tables and views. If you're following security best practices, creating roles and assigning permissions to roles, then you're right, INFORMATION_SCHEMA.TABLE_PRIVILEGES is going to fail you. To see this, let's set up a simple example. I'm using the Testing database here, just a small database I've created for the demo. I'd advise you to create your own so you can walk through the example.

USE Testing;

-- Create a test user to demonstrate the flaw with

CREATE ROLE AssignSecurity;

EXEC sp_addrolemember @membername = 'TestUser', @rolename = 'AssignSecurity';

CREATE TABLE dbo.TestTable (TestID INT);

GRANT SELECT ON dbo.TestTable TO AssignSecurity;

Now that we have a user, a role, a table, and security to query the table assigned to the role, let's use INFORMATION_SCHEMA.TABLE_PRIVILEGES and query for the user's permissions.

-- doesn't show the permissions

We don't get anything back because INFORMATION_SCHEMA.TABLE_PRIVILEGES only handles the explicit case where the user is directly granted the permission. Since the permission is coming from the role TestUser is a member of, nothing comes back. However, the issue isn't just limited to INFORMATION_SCHEMA.TABLE_PRIVILEGES:

-- Querying sys.database_permissions has same issue
SELECT, perm.permission_name, perm.state_desc
FROM sys.database_permissions perm
JOIN sys.database_principals prin
ON perm.grantee_principal_id = prin.principal_id
JOIN sys.objects o
ON perm.major_id = o.object_id
WHERE = 'TestUser';

The issue here is that the grantee, the database user or role that has the permission, is not the user. While the user receives the permission, these specific queries don't do it for us. There are some tools that would work, including a system function, fn_my_permissions(). However, the problem with fn_my_permissions() is you'll have to impersonate the user in question like so:

-- You can fn_my_permissions() instead
-- But you need to impersonate account and specify object specifically

FROM fn_my_permissions('dbo.TestTable', 'OBJECT');


Also, you have to state the object explicitly. Another option is HAS_PERMS_BY_NAME, but this also requires impersonation:

-- Another option is HAS_PERMS_BY_NAME
-- But this also requires you to impersonate the account

FROM sys.objects;


So what can we do? Actually, we do need to query permissions for the roles the user is a member of. This query will do the job, but only if you're not nesting roles. If you are nesting roles, then you're looking at a Common Table Expression to get the list of all roles the user belongs to. But again, let's consider the simplest case, where no roles are nested.

-- The best solution for explicit permissions
SELECT, perm.permission_name, perm.state_desc
FROM sys.database_principals prin
JOIN sys.database_role_members rm
ON prin.principal_id = rm.member_principal_id
JOIN sys.database_permissions perm
ON rm.role_principal_id = perm.grantee_principal_id
JOIN sys.objects o
ON o.object_id = perm.major_id
WHERE = 'TestUser'
SELECT, perm2.permission_name, perm2.state_desc
FROM sys.database_principals prin2
JOIN sys.database_permissions perm2
ON prin2.principal_id = perm2.grantee_principal_id
JOIN sys.objects o2
ON o2.object_id = perm2.major_id
WHERE = 'TestUser';

And there you have it. While the security best practice is to use roles for security, determining what access an individual user has can be a little frustrating. SQL Server isn't alone on this, as we encounter the same issue on Windows. That's why it's really important to design your security model well and simplify it as much as you can so things can be easily understood.

Next Steps

Last Updated: 2012-07-25

get scripts

next tip button

About the author
MSSQLTips author K. Brian Kelley K. Brian Kelley is a SQL Server author and columnist focusing primarily on SQL Server security.

View all my tips
Related Resources

More SQL Server Solutions

Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

Email me updates

Signup for our newsletter

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.

Tuesday, March 18, 2014 - 9:08:52 AM - Richard Goulet Back To Top

Thanks.  I figured this out for Oracle many years ago but hadn't gotten around to SQL*server yet.  Also, the issue that you started with is very prominent in poorly built third party software that comes out of garages.  Cheap price, cheaper product.

Friday, August 03, 2012 - 2:47:53 PM - Elliott Back To Top


Thanks.  Very useful.  I've already put your script into my library (with attribution to you) for future use.



Friday, August 03, 2012 - 1:12:37 PM - Zuma Back To Top

Very useful info Mr. Kelley! This has definitely become part of my bag of tricks.

Thank you.

Thursday, August 02, 2012 - 8:02:47 PM - jeff bennett Back To Top

Very good article.  Thanks!


Recommended Reading

Enabling xp_cmdshell in SQL Server

Encrypting passwords for use with Python and SQL Server

Understanding SQL Server fixed database roles

How to configure SSL encryption in SQL Server

Steps to Drop an Orphan SQL Server User when it owns a Schema or Role

get free sql tips
agree to terms

Learn more about SQL Server tools