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?

techresearch7777777 1,881 Reputation points
2023-11-01T21:57:24.42+00:00

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.

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,972 questions
{count} votes

Accepted answer
  1. Konstantinos Passadis 19,171 Reputation points MVP
    2023-11-02T00:11:04.75+00:00

    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


1 additional answer

Sort by: Most helpful
  1. Konstantinos Passadis 19,171 Reputation points MVP
    2023-11-01T22:46:25.6133333+00:00

    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


Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.