Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Issues Determining an Individual SQL Server User's Permissions


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

Problem

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?

Solution

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
-- INFORMATION_SCHEMA.TABLE_PRIVILEGES
CREATE USER TestUser WITHOUT LOGIN;
GO

CREATE ROLE AssignSecurity;
GO

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

CREATE TABLE dbo.TestTable (TestID INT);
GO

GRANT SELECT ON dbo.TestTable TO AssignSecurity;
GO

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.

-- Querying INFORMATION_SCHEMA.TABLE_PRIVILEGES
-- doesn't show the permissions
SELECT GRANTEE, TABLE_NAME, PRIVILEGE_TYPE
FROM INFORMATION_SCHEMA.TABLE_PRIVILEGES
WHERE GRANTEE = 'TestUser';

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 o.name, 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 prin.name = '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
EXECUTE AS USER = 'TestUser';
GO

SELECT *
FROM fn_my_permissions('dbo.TestTable', 'OBJECT');
GO

REVERT;
GO

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
EXECUTE AS USER = 'TestUser';
GO

SELECT HAS_PERMS_BY_NAME('dbo.TestTable', 'OBJECT', 'SELECT') AS has_SELECT, name
FROM sys.objects;
GO

REVERT;
GO

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 o.name, 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 prin.name = 'TestUser'
UNION
SELECT o2.name, 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 prin2.name = '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




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 (*).

*Name    *Email    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

Brian,

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

Cheers,

Elliott


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!


Learn more about SQL Server tools