Issues Determining an Individual SQL Server User's Permissions

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.

Source Collected from MSSQLTIPS.COM