How To Query for Specific User Access Rights

Question

I have an old database that I am inheriting. The access rights are not clearly defined anywhere and I'm looking for a quick way to get them for everyone. Let's say I have a user in my database that does not belong to any membership roles. However, they have been given access to do specific things to specific tables. For example, they can run select queries on table X and run update queries on table Y. I know I can find out what they have by going to the properties for each user. I would imagine, however, that there has to be a system table somewhere that has all of this defined in it and makes it easily queryable. What would this query look like.

FYI: I am working with SQL Server 2005

Update: Is there also a way to do this for all databases on the server?

Answer 1

Take a look at the Security Catalog Views, then check out MrDenny's answer here which gives a query to list a user's rights. I reproduce it here (tidied up to my liking)..

SELECT  [Schema]            =   sys.schemas.name 
,       [Object]            =   sys.objects.name 
,       username            =   sys.database_principals.name 
,       permissions_type    =   sys.database_permissions.type 
,       permission_name     =   sys.database_permissions.permission_name
,       permission_state    =   sys.database_permissions.state 
,       state_desc          =   sys.database_permissions.state_desc
,       permissionsql       =   state_desc + ' ' + permission_name 
                                 + ' on ['+ sys.schemas.name + '].[' + sys.objects.name 
                                 + '] to [' + sys.database_principals.name + ']' 
                                  COLLATE LATIN1_General_CI_AS 
FROM sys.database_permissions 
 INNER JOIN sys.objects ON sys.database_permissions.major_id =      sys.objects.object_id 
 INNER JOIN sys.schemas ON sys.objects.schema_id = sys.schemas.schema_id 
 INNER JOIN sys.database_principals ON sys.database_permissions.grantee_principal_id =  sys.database_principals.principal_id 
ORDER BY 1, 2, 3, 5

Answer 2

Things are a bit trickier actually. The effective permissions are a combination of internal database permissions (queryable as Denny's query showed above by doza) and windows group membership. Th later unfortunately is stored outside SQL, in the AD schema so you can't realy query it.

So if your goal is to display 'Access to the table X is given to domain\someuser and domain\somegroup and denied to domain\someothergroup' then you can use the catalog metadata and query it, as showed in doza's post.

However if your goal is to answer 'Does user domain\someuser have access to table X?' you can't get the answer from the query above. That's right, despite the fact that you see a record saying the domain\someuser is granted access, you cannot answer if it has effective access. Remember that a single deny trumps all grants, and if domain\user is member of domain\someothergroup group then domain\someuser is effectively denied access.

To answer the later question you must use a different mechanism, namely you have to impersonate the user at SQL level and check the permission via HAS_PERM_BY_NAME:

EXECUTE AS USER = 'domain\someuser';
SELECT HAS_PERMS_BY_NAME('X','TABLE','SELECT');
REVERT;

Is worth noting that the first question can be answered by anyone with view privileges on the security catalogs, while the later requires impersonate permission, a much more powerful privilege.