• Technologies

  • Instructors

  • Who has access? – SQL Server

    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
    ORDER BY type_desc, tablename

    You may also like:  Optional Parameters in SSRS


    Share your thoughts...

    Please fill out the comment form below to post a reply.