SQL Server database roles - tables

sql-server

I try to to find a SQL statement to get an overview of all database roles to table connections. So I am searching for an n:m connection between sys.tables and sys.database_principals where type_desc='DATABASE_ROLE'.

The goal is to have an overview which roles are on which tables. Can anyone help?

I am using Microsoft SQL Server 2019.

Kind regards

Solution 1:

sys.database_permissions is what you are looking for. You can join that to sys.tables and sys.database_principals and get all the info you need

SELECT
  schema_name = s.name,
  t.name,
  per.permission_name,
  prin.name,
  prin.state_desc
FROM sys.tables t
JOIN sys.schemas s ON s.schema_id = t.schema_id
JOIN sys.database_permissions ON per.major_id = t.object_id
    AND per.class = 1  -- object or column
    AND per.minor_id = 0  -- table only, not column
JOIN sys.database_principals prin ON prin.principal_id = per.grantee_principal_id
    AND prin.type = 'R'; -- same as DATABASE_ROLE

SQL Server database roles - tables

Solution 1:

Related

Recent Posts