Listing Denied Permission for table and column level using TRANSACT-SQL RRS feed

  • General discussion

  • I use the following TRANSACT-SQL to list database username, database role, application, table name, schema, permission name and state_desc.

    SELECT u.name, c.name, t.name, s.name, p.permission_name, p.state_desc FROM sys.database_permissions as p
    INNER JOIN sys.sysusers as u ON u.uid = p.grantee_principal_id
    INNER JOIN sys.tables as t ON t.object_id = p.major_id
    INNER JOIN sys.schemas as s ON t.schema_id = s.schema_id
    LEFT JOIN sys.columns as c ON c.column_id = p.minor_id and c.object_id = p.major_id
    WHERE p.state_desc = 'DENY'

    is this a convenient way to do it or are there any nice alternatives?
    Tuesday, July 28, 2009 8:02 AM

All replies