Would it be possible to generate the list of roles and which tables the roles have access to for a particular Schema within a user created SQL Server DB?

Question

Hello, would it be possible to generate the  list of roles and which tables the roles have access to for a particular Schema within a user created SQL Server DB?

Thanks in advance.

Answer 1

Hello techresearch7777777 !

Thanks for answering !

A query that lists all permissions , ( If null means no explicit permissions are set on tables or views)

The Object Type will show you if it is a Table or a View

SELECT 
    DB_NAME() AS DatabaseName,
    SCHEMA_NAME(o.schema_id) AS SchemaName,
    o.name AS ObjectName,
    o.type_desc AS ObjectType,
    dp.permission_name,
    dp.state_desc,
    p.name AS PrincipalName,
    p.type_desc AS PrincipalType
FROM 
    sys.objects o
LEFT JOIN 
    sys.database_permissions dp ON dp.major_id = o.object_id AND dp.minor_id = 0
LEFT JOIN 
    sys.database_principals p ON dp.grantee_principal_id = p.principal_id
WHERE 
    o.type IN ('U', 'V') -- U for Table, V for View
ORDER BY 
    SchemaName, ObjectName, PrincipalName, permission_name;

Another one is the following only for Tables :

SELECT 
    DB_NAME() AS DatabaseName,
    SCHEMA_NAME(t.schema_id) AS SchemaName,
    t.name AS TableName,
    dp.permission_name,
    dp.state_desc,
    p.name AS PrincipalName,
    p.type_desc AS PrincipalType
FROM 
    sys.tables t
INNER JOIN 
    sys.schemas s ON t.schema_id = s.schema_id
LEFT JOIN 
    sys.database_permissions dp ON dp.major_id = t.object_id AND dp.minor_id = 0
LEFT JOIN 
    sys.database_principals p ON dp.grantee_principal_id = p.principal_id
ORDER BY 
    SchemaName, TableName, PrincipalName, permission_name;

And finally this will show you who has what ! : (lists out all the roles in a database, along with their members)

SELECT
  role_principals.name AS RoleName,
  member_principals.name AS MemberName,
  member_principals.type_desc AS MemberType
FROM
  sys.database_role_members role_members
JOIN
  sys.database_principals role_principals ON role_members.role_principal_id = role_principals.principal_id
LEFT JOIN
  sys.database_principals member_principals ON role_members.member_principal_id = member_principals.principal_id
ORDER BY
  RoleName,
  MemberName;

Keep in mind :

• Explicit Permissions: Permissions assigned directly to the user.
  

• Role-Based Permissions: Permissions assigned to roles that the user is a member of.
  

• Inherited Role Memberships: If the user is a member of a role, and that role is a member of another role, the user inherits permissions from both roles.
  

---

I hope this helps!

Kindly mark the answer as Accepted and Upvote in case it helped!

Regards

Answer 2

Hello techresearch7777777 !

Here is a sample for the dbo schema :

SELECT
    rp.name AS RoleName,
    OBJECT_SCHEMA_NAME(p.major_id) AS SchemaName,
    OBJECT_NAME(p.major_id) AS TableName,
    p.permission_name AS Permission
FROM
    sys.database_permissions p
JOIN 
    sys.database_principals rp ON p.grantee_principal_id = rp.principal_id
WHERE
    p.class = 1 -- Object or column
    AND OBJECT_SCHEMA_NAME(p.major_id) = 'dbo' -- Replace with your schema name
    AND rp.type_desc = 'DATABASE_ROLE'
    
ORDER BY
    RoleName, SchemaName, TableName;

And here you can find the DB Schemas

SELECT name AS SchemaName

FROM sys.schemas

ORDER BY SchemaName;

---

I hope this helps!

Kindly mark the answer as Accepted and Upvote in case it helped!

Regards