List all Securable\Permissions on all tables for all roles and owners

Question

I need to make a list of all tables in two or more databases on one server. This list needs to contain the TableName, Permission\Securable, Role\List role name and a Y/N for the permission.

I have tried several different options, however the results are significantly different than the report from last year (query from last year doesn't exist).
I am probably missing something obvious. What is happening is the table doesn't show in the results because there are no explicit permissions. However the report last year displays that there are. So I think I am just using the wrong method all together.

Here is the "Generic" for on the code I have attempted to utilize in my last attempt:

SELECT s.name AS [Schema]
            , o.name AS Object
--          , u.name AS [User]
            , dp.permission_name, dp.state_desc
FROM     sys.database_permissions dp
    JOIN     sys.objects o                      ON dp.major_id = o.object_id
    JOIN     sys.schemas s                      ON o.schema_id = s.schema_id
    JOIN     sys.database_principals u  ON dp.grantee_principal_id = u.principal_id
WHERE o.name = 'tables'--@ObjName

UNION ALL
SELECT s.name AS [Schema]
            --, NULL
            , u.name AS [User]
            , dp.permission_name
            , dp.state_desc
FROM     sys.database_permissions dp
    JOIN     sys.schemas s                      ON dp.major_id = s.schema_id
    JOIN     sys.database_principals u  ON dp.grantee_principal_id = u.principal_id
--ORDER    BY s.name, o.name --, u.name

possible duplicate of [SQL Server query to find all permissions/access for all users in a database](http://stackoverflow.com/questions/7048839/sql-server-query-to-find-all-permissions-access-for-all-users-in-a-database) — Bernd Linde, Mar 18 '15 at 15:05

score 0 · Accepted Answer · answered Mar 30 '15 at 16:01

0

What I was Missing was Implied permissions Versus Explicit Permissions. I assumed explicted permissions were used, not implied\inheritied permissions.

answered Mar 30 '15 at 16:01

Swoozie

11
6

score 0 · Answer 2 · answered Sep 14 '16 at 15:32

I made this query to view the permissions of a role on the tables and the views.

DECLARE @RoleName VARCHAR(MAX) = 'role_gestionale'

IF OBJECT_ID('tempdb..#roles') IS NOT NULL DROP TABLE #roles
SELECT dp.major_id Object_id, dp.permission_name Permission
INTO #roles
FROM     sys.database_permissions dp
    INNER JOIN     sys.database_principals u  ON dp.grantee_principal_id = u.principal_id
WHERE u.name=@RoleName


SELECT *
FROM (
    SELECT o.type_desc Tipo, s.name AS [SCHEMA], o.name AS [Table]
        , (SELECT COUNT(*) FROM #roles r WHERE r.Object_id=o.object_id AND Permission='SELECT') AS [SELECT]
        , (SELECT COUNT(*) FROM #roles r WHERE r.Object_id=o.object_id AND Permission='INSERT') AS [INSERT]
        , (SELECT COUNT(*) FROM #roles r WHERE r.Object_id=o.object_id AND Permission='UPDATE') AS [UPDATE]
        , (SELECT COUNT(*) FROM #roles r WHERE r.Object_id=o.object_id AND Permission='DELETE') AS [DELETE]
    FROM sys.objects o
        INNER JOIN sys.schemas s                      ON o.schema_id = s.schema_id
    WHERE o.type_desc IN ('USER_TABLE', 'VIEW')
) a
--WHERE [INSERT]=0 OR [SELECT]=0 OR  [UPDATE]=0 OR [DELETE]=0
ORDER BY 1, 2, 3

Alessandro Lettieri

List all Securable\Permissions on all tables for all roles and owners

2 Answers2