I'm doing the administration of some databases that were created before my arrival.
In most of them a role called dp_role_analista was created, and some users are in that role.
How do I know what permissions this role is granting to users?
The script below lists all users and their roles, both database users and server users, identifying the nature of the user.
Should be run in 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 the 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