Who has access? – SQL Server

Like This Blog 0

Added by Jeff Jones July 11, 2007

Occasionally you would like to find out all the tables, views and stored procedures a user can access and what permissions they have.  With a combination of the new EXECUTE AS command and the new HAS_PERMS_BY_NAME function you can figure this out.  Below is a query that uses the sys.All_Objects metadata view to generate object names.  Then it passes the object names through the new function with some additional parameters (the securable type, and permission type) and it returns 1 if the user has that permission and 0 if they do not.

The result shows both implicit permissions granted through fixed server or database roles. It also shows explicit permission granted though user-defined roles or granted directly to the database user account.

The EXECUTE AS can only specify individual user principal defined to SQL Server.  It cannot reference a Windows Group.  This is only valid with SQL Server 2005.

EXECUTE AS LOGIN = 'miami\anders'

SELECT SCHEMA_NAME(schema_id) + '.' + name TableName
, type_desc
, HAS_PERMS_BY_NAME(SCHEMA_NAME(schema_id) + '.' + name,
'OBJECT', 'SELECT') AS have_select
, HAS_PERMS_BY_NAME(SCHEMA_NAME(schema_id) + '.' + name,
'OBJECT', 'UPDATE') AS have_update
, HAS_PERMS_BY_NAME(SCHEMA_NAME(schema_id) + '.' + name,
'OBJECT', 'INSERT') AS have_insert
, HAS_PERMS_BY_NAME(SCHEMA_NAME(schema_id) + '.' + name,
'OBJECT', 'DELETE') AS have_delete
, HAS_PERMS_BY_NAME(SCHEMA_NAME(schema_id) + '.' + name,
'OBJECT', 'EXECUTE') AS have_execute
FROM sys.all_objects
WHERE type_desc IN ('USER_TABLE', 'SQL_STORED_PROCEDURE', 'VIEW')
AND SCHEMA_NAME(schema_id) NOT IN ('sys', 'INFORMATION_SCHEMA')
ORDER BY type_desc, tablename

REVERT