How to know the permissions of a scroll in SQL Server

Question

I am administering some Databases that were created before my arrival.

In most of them was created a role called dp_role_analista, and some users are in this role.

How do I know what permissions this scroll is granting to users?

Answer 1

The script below lists all users and their respective roles, both database users and server users, identifying the nature of the user.

Must be executed in the database master:

SELECT 
[LOGIN TYPE]=
    CASE SP.TYPE
        WHEN 'U' THEN 'WINDOWS'
        WHEN 'S' THEN 'SQLSERVER'
        WHEN 'G' THEN 'GROUP'
    END,
    CONVERT(CHAR(45), SP.NAME) AS SRVLOGIN, 
    CONVERT(CHAR(45), SP2.NAME) AS SRVROLE,
    CONVERT(CHAR(25), DBP.NAME) AS DBUSER,
    CONVERT(CHAR(25), DBP2.NAME) AS DBROLE
FROM SYS.SERVER_PRINCIPALS AS SP 
    JOIN SYS.DATABASE_PRINCIPALS AS DBP ON SP.SID = DBP.SID 
    JOIN SYS.DATABASE_ROLE_MEMBERS AS DBRM ON DBP.PRINCIPAL_ID = DBRM.MEMBER_PRINCIPAL_ID 
    JOIN SYS.DATABASE_PRINCIPALS AS DBP2 ON DBRM.ROLE_PRINCIPAL_ID = DBP2.PRINCIPAL_ID 
    LEFT JOIN SYS.SERVER_ROLE_MEMBERS AS SRM ON SP.PRINCIPAL_ID = SRM.MEMBER_PRINCIPAL_ID 
    LEFT JOIN SYS.SERVER_PRINCIPALS AS SP2 ON SRM.ROLE_PRINCIPAL_ID = SP2.PRINCIPAL_ID

The script below lists all permissions of a role:

SELECT DISTINCT RP.NAME, 
                OBJECTTYPE = RP.TYPE_DESC, 
                PERMISSIONTYPE = PM.CLASS_DESC, 
                PM.PERMISSION_NAME, 
                PM.STATE_DESC, 
                OBJECTTYPE = CASE 
                               WHEN OBJ.TYPE_DESC IS NULL 
                                     OR OBJ.TYPE_DESC = 'SYSTEM_TABLE' THEN 
                               PM.CLASS_DESC 
                               ELSE OBJ.TYPE_DESC 
                             END, 
                [OBJECTNAME] = ISNULL(SS.NAME, OBJECT_NAME(PM.MAJOR_ID)) 
FROM   SYS.DATABASE_PRINCIPALS RP 
       INNER JOIN SYS.DATABASE_PERMISSIONS PM 
               ON PM.GRANTEE_PRINCIPAL_ID = RP.PRINCIPAL_ID 
       LEFT JOIN SYS.SCHEMAS SS 
              ON PM.MAJOR_ID = SS.SCHEMA_ID 
       LEFT JOIN SYS.OBJECTS OBJ 
              ON PM.[MAJOR_ID] = OBJ.[OBJECT_ID] 
WHERE  RP.TYPE_DESC = 'DATABASE_ROLE' 
       AND PM.CLASS_DESC <> 'DATABASE' 
ORDER  BY RP.NAME, 
          RP.TYPE_DESC, 
          PM.CLASS_DESC