22

I've searched around all over and haven't found a conclusive answer to this question.

I need a script that can give ALL permissions for an associated role.

Any thoughts, or is it even possible?

This gets me CLOSE - but I can't seem to flip it around and give the summary for roles, rather than users.

http://consultingblogs.emc.com/jamiethomson/archive/2007/02/09/SQL-Server-2005_3A00_-View-all-permissions--_2800_2_2900_.aspx

 WITH    perms_cte as
(
        select USER_NAME(p.grantee_principal_id) AS principal_name,
                dp.principal_id,
                dp.type_desc AS principal_type_desc,
                p.class_desc,
                OBJECT_NAME(p.major_id) AS object_name,
                p.permission_name,
                p.state_desc AS permission_state_desc
        from    sys.database_permissions p
        inner   JOIN sys.database_principals dp
        on     p.grantee_principal_id = dp.principal_id
)
--role members
SELECT rm.member_principal_name, rm.principal_type_desc, p.class_desc, 
    p.object_name, p.permission_name, p.permission_state_desc,rm.role_name
FROM    perms_cte p
right outer JOIN (
    select role_principal_id, dp.type_desc as principal_type_desc, 
   member_principal_id,user_name(member_principal_id) as member_principal_name,
   user_name(role_principal_id) as role_name--,*
    from    sys.database_role_members rm
    INNER   JOIN sys.database_principals dp
    ON     rm.member_principal_id = dp.principal_id
) rm
ON     rm.role_principal_id = p.principal_id
order by 1

migrated from stackoverflow.com Mar 13 '13 at 19:08

This question came from our site for professional and enthusiast programmers.

19

We came up with this, which seems to work:

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, 
                s.Name as SchemaName,
                [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] 
       LEFT JOIN sys.schemas s
              ON s.schema_id = obj.schema_id
WHERE  rp.type_desc = 'DATABASE_ROLE' 
       AND pm.class_desc <> 'DATABASE' 
ORDER  BY rp.name, 
          rp.type_desc, 
          pm.class_desc 
  • Added field SchemaName and a left join to schemas from objects. I didn't understand the purpose of the first left join to schemas, its always null here. – crokusek May 5 '16 at 22:06
  • But if a database role is granted permissions to all objects of a certain type e.g. "GRANT SELECT TO Some_DB_Role", this script wont display that? – Jhunter1 Aug 22 at 12:49
13

I can't find the reference, but here is a very descriptive script (I have had it in my repository for years now - very useful for auditing):

/*


--Script source found at :  http://stackoverflow.com/a/7059579/1387418
Security Audit Report
1) List all access provisioned to a sql user or windows user/group directly 
2) List all access provisioned to a sql user or windows user/group through a database or application role
3) List all access provisioned to the public role

Columns Returned:
UserName        : SQL or Windows/Active Directory user cccount.  This could also be an Active Directory group.
UserType        : Value will be either 'SQL User' or 'Windows User'.  This reflects the type of user defined for the 
                  SQL Server user account.
DatabaseUserName: Name of the associated user as defined in the database user account.  The database user may not be the
                  same as the server user.
Role            : The role name.  This will be null if the associated permissions to the object are defined at directly
                  on the user account, otherwise this will be the name of the role that the user is a member of.
PermissionType  : Type of permissions the user/role has on an object. Examples could include CONNECT, EXECUTE, SELECT
                  DELETE, INSERT, ALTER, CONTROL, TAKE OWNERSHIP, VIEW DEFINITION, etc.
                  This value may not be populated for all roles.  Some built in roles have implicit permission
                  definitions.
PermissionState : Reflects the state of the permission type, examples could include GRANT, DENY, etc.
                  This value may not be populated for all roles.  Some built in roles have implicit permission
                  definitions.
ObjectType      : Type of object the user/role is assigned permissions on.  Examples could include USER_TABLE, 
                  SQL_SCALAR_FUNCTION, SQL_INLINE_TABLE_VALUED_FUNCTION, SQL_STORED_PROCEDURE, VIEW, etc.   
                  This value may not be populated for all roles.  Some built in roles have implicit permission
                  definitions.          
ObjectName      : Name of the object that the user/role is assigned permissions on.  
                  This value may not be populated for all roles.  Some built in roles have implicit permission
                  definitions.
ColumnName      : Name of the column of the object that the user/role is assigned permissions on. This value
                  is only populated if the object is a table, view or a table value function.                 
*/

--List all access provisioned to a sql user or windows user/group directly 
SELECT  
    [UserName] = CASE princ.[type] 
                    WHEN 'S' THEN princ.[name]
                    WHEN 'U' THEN ulogin.[name] COLLATE Latin1_General_CI_AI
                 END,
    [UserType] = CASE princ.[type]
                    WHEN 'S' THEN 'SQL User'
                    WHEN 'U' THEN 'Windows User'
                 END,  
    [DatabaseUserName] = princ.[name],       
    [Role] = null,      
    [PermissionType] = perm.[permission_name],       
    [PermissionState] = perm.[state_desc],       
    [ObjectType] = obj.type_desc,--perm.[class_desc],       
    [ObjectName] = OBJECT_NAME(perm.major_id),
    [ColumnName] = col.[name]
FROM    
    --database user
    sys.database_principals princ  
LEFT JOIN
    --Login accounts
    sys.login_token ulogin on princ.[sid] = ulogin.[sid]
LEFT JOIN        
    --Permissions
    sys.database_permissions perm ON perm.[grantee_principal_id] = princ.[principal_id]
LEFT JOIN
    --Table columns
    sys.columns col ON col.[object_id] = perm.major_id 
                    AND col.[column_id] = perm.[minor_id]
LEFT JOIN
    sys.objects obj ON perm.[major_id] = obj.[object_id]
WHERE 
    princ.[type] in ('S','U')
UNION
--List all access provisioned to a sql user or windows user/group through a database or application role
SELECT  
    [UserName] = CASE memberprinc.[type] 
                    WHEN 'S' THEN memberprinc.[name]
                    WHEN 'U' THEN ulogin.[name] COLLATE Latin1_General_CI_AI
                 END,
    [UserType] = CASE memberprinc.[type]
                    WHEN 'S' THEN 'SQL User'
                    WHEN 'U' THEN 'Windows User'
                 END, 
    [DatabaseUserName] = memberprinc.[name],   
    [Role] = roleprinc.[name],      
    [PermissionType] = perm.[permission_name],       
    [PermissionState] = perm.[state_desc],       
    [ObjectType] = obj.type_desc,--perm.[class_desc],   
    [ObjectName] = OBJECT_NAME(perm.major_id),
    [ColumnName] = col.[name]
FROM    
    --Role/member associations
    sys.database_role_members members
JOIN
    --Roles
    sys.database_principals roleprinc ON roleprinc.[principal_id] = members.[role_principal_id]
JOIN
    --Role members (database users)
    sys.database_principals memberprinc ON memberprinc.[principal_id] = members.[member_principal_id]
LEFT JOIN
    --Login accounts
    sys.login_token ulogin on memberprinc.[sid] = ulogin.[sid]
LEFT JOIN        
    --Permissions
    sys.database_permissions perm ON perm.[grantee_principal_id] = roleprinc.[principal_id]
LEFT JOIN
    --Table columns
    sys.columns col on col.[object_id] = perm.major_id 
                    AND col.[column_id] = perm.[minor_id]
LEFT JOIN
    sys.objects obj ON perm.[major_id] = obj.[object_id]
UNION
--List all access provisioned to the public role, which everyone gets by default
SELECT  
    [UserName] = '{All Users}',
    [UserType] = '{All Users}', 
    [DatabaseUserName] = '{All Users}',       
    [Role] = roleprinc.[name],      
    [PermissionType] = perm.[permission_name],       
    [PermissionState] = perm.[state_desc],       
    [ObjectType] = obj.type_desc,--perm.[class_desc],  
    [ObjectName] = OBJECT_NAME(perm.major_id),
    [ColumnName] = col.[name]
FROM    
    --Roles
    sys.database_principals roleprinc
LEFT JOIN        
    --Role permissions
    sys.database_permissions perm ON perm.[grantee_principal_id] = roleprinc.[principal_id]
LEFT JOIN
    --Table columns
    sys.columns col on col.[object_id] = perm.major_id 
                    AND col.[column_id] = perm.[minor_id]                   
JOIN 
    --All objects   
    sys.objects obj ON obj.[object_id] = perm.[major_id]
WHERE
    --Only roles
    roleprinc.[type] = 'R' AND
    --Only public role
    roleprinc.[name] = 'public' AND
    --Only objects of ours, not the MS objects
    obj.is_ms_shipped = 0
ORDER BY
    princ.[Name],
    OBJECT_NAME(perm.major_id),
    col.[name],
    perm.[permission_name],
    perm.[state_desc],
    obj.type_desc--perm.[class_desc] 
10

This I think should do it, replace 'blah' with either your role name or a database user (note that built in roles don't show as having any permissions):

SELECT DB_NAME() AS 'DBName'
      ,p.[name] AS 'PrincipalName'
      ,p.[type_desc] AS 'PrincipalType'
      ,p2.[name] AS 'GrantedBy'
      ,dbp.[permission_name]
      ,dbp.[state_desc]
      ,so.[Name] AS 'ObjectName'
      ,so.[type_desc] AS 'ObjectType'
  FROM [sys].[database_permissions] dbp LEFT JOIN [sys].[objects] so
    ON dbp.[major_id] = so.[object_id] LEFT JOIN [sys].[database_principals] p
    ON dbp.[grantee_principal_id] = p.[principal_id] LEFT JOIN [sys].[database_principals] p2
    ON dbp.[grantor_principal_id] = p2.[principal_id]

WHERE p.[name] = 'blah'
  • kind of... it just doesn't seem to include the object name/type. For a particular role I'm looking at that has execute, and a couple other permissions on a schema, it shows null. Two rows show ObjectName being sysrowsets and ObjectType being SYSTEM_TABLE. – elgabito Mar 7 '13 at 19:15
4

Just to add to the list I've got a couple of SPs sp_dbpermissions and sp_srvpermissions that can be used to return the same type of information.

You can run

EXEC sp_dbpermissions 'dbname','rolename (or any other principal really)'

And get this

enter image description here

(You can't see it but the grant script is off to the right on the third data set and drop/create scripts off to the right on the first data set.)

3

This is my effort. I have a longer script which loops through all of the DBs too, let me know if that's more useful:

SELECT 
@@Servername as ServerName
,DB_NAME() AS DatabaseName
,d.name AS DatabaseUser
,ISNULL(dr.name, 'Public') AS DatabaseRole
,dp.permission_name as AdditionalPermission
,dp.state_desc AS PermissionState
,ISNULL(o.type_desc, 'N/A')  AS ObjectType
,ISNULL(o.name, 'N/A') AS ObjectName
FROM sys.database_principals d
    LEFT JOIN sys.database_role_members r
        ON d.principal_id = r.member_principal_id 
    LEFT JOIN sys.database_principals dr
        ON r.role_principal_id = dr.principal_id 
    left JOIN   sys.database_permissions dp
        ON d.principal_id = dp.grantee_principal_id
    LEFT JOIN sys.objects o
        ON dp.major_id = o.object_id 
  • I removed the temp table. Sorry about that! – JYatesDBA May 19 '17 at 10:56
  • This is great for one DB, could I see how you looped through all databases? – tree Jan 24 at 13:35
3

just to add to the accepted answer, a role can sometimes belong to another role.

DECLARE @ROLE VARCHAR(108)
select @role = 'db_BodenProcessor'

;WITH theRoles (member_principal_id, role_principal_id) 
AS 
(
  SELECT 
   r.member_principal_id, 
   r.role_principal_id
  FROM sys.database_role_members r 
   UNION ALL
  SELECT 
   tr.member_principal_id, 
   rm.role_principal_id
  FROM sys.database_role_members rm 
            INNER JOIN theRoles tr 
                    ON rm.member_principal_id = tr.role_principal_id
)

--select * from theRoles


select  the_role=A.name
       ,A.is_fixed_role
       ,role_member=B.name
       ,B.type_desc
from theRoles tr
        INNER JOIN sys.database_principals A 
                ON tr.role_principal_id = A.principal_id
        INNER JOIN sys.database_principals B 
                ON tr.member_principal_id = B.principal_id
WHERE A.NAME = @ROLE
   OR B.name = @ROLE
GROUP BY A.name
       ,A.is_fixed_role
       ,B.name
       ,B.type_desc
order by a.is_fixed_role DESC
        ,a.name

enter image description here

3

Since this popped back up via the Community bot, I'll throw my script into the hat as it's pretty exhaustive and I haven't come across anything that it's not identified. The bonus is the output is nicely formatted and allows for quite extensive database roles as well:

/********************************************************************
 *                                                                  *
 * Author: John Eisbrener                                           *
 * Script Purpose: Script out Database Role Definition              *
 * Notes: Please report any bugs to http://www.dbaeyes.com/         *
 *                                                                  *
 * Update: 2014-03-03 - Adjusted output to accommodate Role         *
 *                      definitions that are longer than 8000 chars *
 * Update: 2013-09-03 - Added user output per Joe Spivey's comment  *
 *                    - Modified formatting for oddly named objects *
 *                    - Included support for Grants on DMVs         *
 ********************************************************************/
DECLARE @roleName VARCHAR(255)
SET @roleName = 'DatabaseRoleName'

-- Script out the Role
DECLARE @roleDesc VARCHAR(MAX), @crlf VARCHAR(2)
SET @crlf = CHAR(13) + CHAR(10)
SET @roleDesc = 'CREATE ROLE [' + @roleName + ']' + @crlf + 'GO' + @crlf + @crlf

SELECT    @roleDesc = @roleDesc +
        CASE dp.state
            WHEN 'D' THEN 'DENY '
            WHEN 'G' THEN 'GRANT '
            WHEN 'R' THEN 'REVOKE '
            WHEN 'W' THEN 'GRANT '
        END + 
        dp.permission_name + ' ' +
        CASE dp.class
            WHEN 0 THEN ''
            WHEN 1 THEN --table or column subset on the table
                CASE WHEN dp.major_id < 0 THEN
                    + 'ON [sys].[' + OBJECT_NAME(dp.major_id) + '] '
                ELSE
                    + 'ON [' +
                    (SELECT SCHEMA_NAME(schema_id) + '].[' + name FROM sys.objects WHERE object_id = dp.major_id)
                        + -- optionally concatenate column names
                    CASE WHEN MAX(dp.minor_id) > 0 
                         THEN '] ([' + REPLACE(
                                        (SELECT name + '], [' 
                                         FROM sys.columns 
                                         WHERE object_id = dp.major_id 
                                            AND column_id IN (SELECT minor_id 
                                                              FROM sys.database_permissions 
                                                              WHERE major_id = dp.major_id
                                                                AND USER_NAME(grantee_principal_id) IN (@roleName)
                                                             )
                                         FOR XML PATH('')
                                        ) --replace final square bracket pair
                                    + '])', ', []', '')
                         ELSE ']'
                    END + ' '
                END
            WHEN 3 THEN 'ON SCHEMA::[' + SCHEMA_NAME(dp.major_id) + '] '
            WHEN 4 THEN 'ON ' + (SELECT RIGHT(type_desc, 4) + '::[' + name FROM sys.database_principals WHERE principal_id = dp.major_id) + '] '
            WHEN 5 THEN 'ON ASSEMBLY::[' + (SELECT name FROM sys.assemblies WHERE assembly_id = dp.major_id) + '] '
            WHEN 6 THEN 'ON TYPE::[' + (SELECT name FROM sys.types WHERE user_type_id = dp.major_id) + '] '
            WHEN 10 THEN 'ON XML SCHEMA COLLECTION::[' + (SELECT SCHEMA_NAME(schema_id) + '.' + name FROM sys.xml_schema_collections WHERE xml_collection_id = dp.major_id) + '] '
            WHEN 15 THEN 'ON MESSAGE TYPE::[' + (SELECT name FROM sys.service_message_types WHERE message_type_id = dp.major_id) + '] '
            WHEN 16 THEN 'ON CONTRACT::[' + (SELECT name FROM sys.service_contracts WHERE service_contract_id = dp.major_id) + '] '
            WHEN 17 THEN 'ON SERVICE::[' + (SELECT name FROM sys.services WHERE service_id = dp.major_id) + '] '
            WHEN 18 THEN 'ON REMOTE SERVICE BINDING::[' + (SELECT name FROM sys.remote_service_bindings WHERE remote_service_binding_id = dp.major_id) + '] '
            WHEN 19 THEN 'ON ROUTE::[' + (SELECT name FROM sys.routes WHERE route_id = dp.major_id) + '] '
            WHEN 23 THEN 'ON FULLTEXT CATALOG::[' + (SELECT name FROM sys.fulltext_catalogs WHERE fulltext_catalog_id = dp.major_id) + '] '
            WHEN 24 THEN 'ON SYMMETRIC KEY::[' + (SELECT name FROM sys.symmetric_keys WHERE symmetric_key_id = dp.major_id) + '] '
            WHEN 25 THEN 'ON CERTIFICATE::[' + (SELECT name FROM sys.certificates WHERE certificate_id = dp.major_id) + '] '
            WHEN 26 THEN 'ON ASYMMETRIC KEY::[' + (SELECT name FROM sys.asymmetric_keys WHERE asymmetric_key_id = dp.major_id) + '] '
         END COLLATE SQL_Latin1_General_CP1_CI_AS
         + 'TO [' + @roleName + ']' + 
         CASE dp.state WHEN 'W' THEN ' WITH GRANT OPTION' ELSE '' END + @crlf
FROM    sys.database_permissions dp
WHERE    USER_NAME(dp.grantee_principal_id) IN (@roleName)
GROUP BY dp.state, dp.major_id, dp.permission_name, dp.class

SELECT @roleDesc = @roleDesc + 'GO' + @crlf + @crlf      

-- Display users within Role.  Code stubbed by Joe Spivey
SELECT  @roleDesc = @roleDesc + 'EXECUTE sp_AddRoleMember ''' + roles.name + ''', ''' + users.name + '''' + @crlf
FROM    sys.database_principals users
        INNER JOIN sys.database_role_members link 
            ON link.member_principal_id = users.principal_id
        INNER JOIN sys.database_principals roles 
            ON roles.principal_id = link.role_principal_id
WHERE   roles.name = @roleName

-- PRINT out in blocks of up to 8000 based on last \r\n
DECLARE @printCur INT
SET @printCur = 8000

WHILE LEN(@roleDesc) > 8000
BEGIN
    -- Reverse first 8000 characters and look for first lf cr (reversed crlf) as delimiter
    SET @printCur = 8000 - CHARINDEX(CHAR(10) + CHAR(13), REVERSE(SUBSTRING(@roleDesc, 0, 8000)))

    PRINT LEFT(@roleDesc, @printCur)
    SELECT @roleDesc = RIGHT(@roleDesc, LEN(@roleDesc) - @printCur)
END

-- Output new permissions
PRINT @roleDesc + 'GO'

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.