PBM : role has permission to specific tables

Question

In a couple of SQL 2014 databases on the instance, for a database role “ProductGroup”, I need to validate that it has select permissions to all tables named ‘Product%’. If exists any table named ‘Product%’ for which it does not have, then flag it. I was thinking on using PBM instead of a job. However, I was not able to find neither a role property or a table property for which I can write a condition to correlate them in order to achieve my goal.
Do you have any idea if any way to use PBM for this?

Thank you

Answer 1

I can't say that I have much faith in Policy-Based Management. I have not research whether it would be possible to write such a policy.

Here is a query for the task:

SELECT s.name + '.' + o.name
FROM   sys.tables o
JOIN   sys.schemas s ON o.schema_id = s.schema_id
WHERE  o.name LIKE 'Product%'
  AND  NOT EXISTS (SELECT *
                   FROM   sys.database_permissions dp
                   JOIN   sys.database_principals u ON dp.grantee_principal_id = u.principal_id
                   WHERE  dp.major_id = o.object_id
                     AND  dp.type = 'SL'
                     AND  dp.state = 'G'
                     AND  u.name = 'YourRole')

Although this query only covers SELECT permissions granted directly to the role and directly on the object. That is, it does consider permissions granted other roles the role is a member of, and nor does it consider permissions on database or schema level.

Answer 2

Hi @rodrag ,

I am not familiar about changing the SQL script.

I found a script about showing permission on the schema and object.

SELECT
state_desc + ' ' + permission_name +
' on ['+ ss.name + '].[' + so.name + ']
to [' + sdpr.name + ']'
COLLATE LATIN1_General_CI_AS as [Permissions T-SQL]
FROM SYS.DATABASE_PERMISSIONS AS sdp
JOIN sys.objects AS so
ON sdp.major_id = so.OBJECT_ID
JOIN SYS.SCHEMAS AS ss
ON so.SCHEMA_ID = ss.SCHEMA_ID
JOIN SYS.DATABASE_PRINCIPALS AS sdpr
ON sdp.grantee_principal_id = sdpr.principal_id
where 1=1
AND so.name = 'ItemStock'

UNION

SELECT
state_desc + ' ' + permission_name +
' on Schema::['+ ss.name + ']
to [' + sdpr.name + ']'
COLLATE LATIN1_General_CI_AS as [Permissions T-SQL]
FROM SYS.DATABASE_PERMISSIONS AS sdp
JOIN SYS.SCHEMAS AS ss
ON sdp.major_id = ss.SCHEMA_ID
AND sdp.class_desc = 'Schema'
JOIN SYS.DATABASE_PRINCIPALS AS sdpr
ON sdp.grantee_principal_id = sdpr.principal_id
where 1=1

order by [Permissions T-SQL]
GO

Best regards,
Cathy