retrieve SQL server user and role permission

I have to add use <db name>;
go

at the beginning.

to make it keep checking out record.

but I don't see your script return permission assigned on ROLE level, right?

"That should be good to provide details as per your question"

I am very clear enough here:

"right now we want to review all user permission of SQL server ( all SQL server), we would like to review the update/execute permission on SP."

we would like to review that and take away as much execute and update permission from user as possible.

we have a lot of database role on one of the user database we are concerning, and I add this condition to filter out the ROLE level permission which is not NULL,

         AND obj.[is_ms_shipped] = 0 and roleprinc.[name] <> 'Null'


which seems not working well, Role still have null result set return.

if I use "use master;' at the top of your script, the result set is so little.

seems can play around this order by statement:

 ORDER BY
     [UserType],
     [DatabaseUserName],
     [LoginName],
     [Role],
     [Schema],
     [ObjectName],
     [ColumnName],
     [PermissionType],
     [PermissionState],
     [ObjectType] 
    

Select allOpen in new window

sort by role ?

 ORDER BY
[Role],
     [UserType],
     [DatabaseUserName],
     [LoginName],
     
     [Schema],
     [ObjectName],
     [ColumnName],
     [PermissionType],
     [PermissionState],
     [ObjectType]

?

"Also the query that returns user permissions will get all not just for SP EXECUTE/ALTER as you wanted so you can modify that "

I am sorry and I am not sure if I understand this.

you mean my script return more than what I said I want? or it is not enough and that's why you suggest your script ?

I tried your script, it seems don't return all permission on a particular schema, I changed the where condition to:

  AND obj.[is_ms_shipped] = 0 and roleprinc.[name] <> 'Null' and objschem.[name] = 'call'

Select allOpen in new window

it don't give the permission related to the schema 'call'

oh I am sorry, we only concern SQL user/window domain user and role permission on schema and table object: Table, SP, function and nothing more, right?

sorry for late reply - yes, I think your script did not returned the ALTER PROCEDURE permissions as per  your requirement to "...review the update/execute permission on SP." where my understanding is that the "update" would be the ALTER and sorry if I got that wrong.

And yes, the security audit report should give you more that just "... to review the update/execute permission on SP."

I am sorry, Yes is for ?

"sorry for late reply - yes, I think your script did not returned the ALTER PROCEDURE permissions as per  your requirement to"


so your script will do it ? so we need to list out alter /alter procedure granted object too ?

"security audit report"

that one you are referring to is your script, right?

but there seems bugs in the script:

1)but I don't see your script return permission assigned on ROLE level, right?  
2) sort by role ?

  ORDER BY
 [Role],
      [UserType],
      [DatabaseUserName],
      [LoginName],
     
      [Schema],
      [ObjectName],
      [ColumnName],
      [PermissionType],
      [PermissionState],
      [ObjectType]

 ?

I play around the order by role, but I don't see all role displayed well for my DB.

I am sorry that it seems your report don't handle/filter schema well?

I tried in the WHERE clause:

    WHERE
         roleprinc.[type] = 'R'
         AND roleprinc.[name] = 'public'
         AND obj.[is_ms_shipped] = 0 and roleprinc.[name] <> 'Null' and objschem.[name] = 'call

Select allOpen in new window

can't display by just objschem.[name] = 'call , any suggest ?

I can't see why even I do this:

     WHERE
      /*   roleprinc.[type] = 'R'
         AND roleprinc.[name] = 'public'
         AND obj.[is_ms_shipped] = 0 and roleprinc.[name] <> 'Null' and */
         objschem.[name] = 'call'  -- list a particular schema doesn't work.

I can't list all permission by schema 'call'

In view sys.database_principals you have all database users and database roles. type='S' means it's a user, type='R' means it's a role. Join with sys.server_principals to get the corresponding login for each user.

In sys.database_role_members you can find which user is in which role (table contains principal_id for role and user who is member of the role)

In sys.database_permissions you the permissions for objects in the database. MSDN Library about sys.database_permissions: http://msdn.microsoft.com/en-us/library/ms188367(SQL.90).aspx

You should also go through the https://msdn.microsoft.com/en-IN/library/ms176097.aspx

"In view sys.database_principals you have all database users and database roles. type='S' means it's a user, type='R' means it's a role. Join with sys.server_principals to get the corresponding login for each user.
"

I did that too by adding the 'R' but it doesn't work:

/*
 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:
 UserType        : Value will be either 'SQL User', 'Windows User', or 'Windows Group'.
                   This reflects the type of user/group defined for the SQL Server 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.
 LoginName       : SQL or Windows/Active Directory user account.  This could also be an Active Directory group.
 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.
 Schema          : Name of the schema the object is in.
 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.
 */

     --1) List all access provisioned to a SQL user or Windows user/group directly
     
     use Virginia_Development;
     go
     
     SELECT
         [UserType] = CASE princ.[type]
                          WHEN 'S' THEN 'SQL User'
                          WHEN 'U' THEN 'Windows User'
                          WHEN 'G' THEN 'Windows Group'
                          WHEN 'R' THEN 'Database Role'
                      END,
         [DatabaseUserName] = princ.[name],
         [LoginName]        = ulogin.[name],
         [Role]             = NULL,
         [PermissionType]   = perm.[permission_name],
         [PermissionState]  = perm.[state_desc],
         [ObjectType] = CASE perm.[class]
                            WHEN 1 THEN obj.[type_desc]        -- Schema-contained objects
                            ELSE perm.[class_desc]             -- Higher-level objects
                        END,
         [Schema] = objschem.[name],
         [ObjectName] = CASE perm.[class]
                            WHEN 3 THEN permschem.[name]       -- Schemas
                            WHEN 4 THEN imp.[name]             -- Impersonations
                            ELSE OBJECT_NAME(perm.[major_id])  -- General objects
                        END,
         [ColumnName] = col.[name]
     FROM
         --Database user
         sys.database_principals            AS princ
         --Login accounts
         LEFT JOIN sys.server_principals    AS ulogin    ON ulogin.[sid] = princ.[sid]
         --Permissions
         LEFT JOIN sys.database_permissions AS perm      ON perm.[grantee_principal_id] = princ.[principal_id]
         LEFT JOIN sys.schemas              AS permschem ON permschem.[schema_id] = perm.[major_id]
         LEFT JOIN sys.objects              AS obj       ON obj.[object_id] = perm.[major_id]
         LEFT JOIN sys.schemas              AS objschem  ON objschem.[schema_id] = obj.[schema_id]
         --Table columns
         LEFT JOIN sys.columns              AS col       ON col.[object_id] = perm.[major_id]
                                                            AND col.[column_id] = perm.[minor_id]
         --Impersonations
         LEFT JOIN sys.database_principals  AS imp       ON imp.[principal_id] = perm.[major_id]
     WHERE
         princ.[type] IN ('S','U','G','R')
         -- No need for these system accounts
         AND princ.[name] NOT IN ('sys', 'INFORMATION_SCHEMA')
       --   and objschem.[name] = 'call'

 UNION

     --2) List all access provisioned to a SQL user or Windows user/group through a database or application role
     SELECT
         [UserType] = CASE membprinc.[type]
                          WHEN 'S' THEN 'SQL User'
                          WHEN 'U' THEN 'Windows User'
                          WHEN 'G' THEN 'Windows Group'
                          WHEN 'R' THEN 'Database Role'
                      END,
         [DatabaseUserName] = membprinc.[name],
         [LoginName]        = ulogin.[name],
         [Role]             = roleprinc.[name],
         [PermissionType]   = perm.[permission_name],
         [PermissionState]  = perm.[state_desc],
         [ObjectType] = CASE perm.[class]
                            WHEN 1 THEN obj.[type_desc]        -- Schema-contained objects
                            ELSE perm.[class_desc]             -- Higher-level objects
                        END,
         [Schema] = objschem.[name],
         [ObjectName] = CASE perm.[class]
                            WHEN 3 THEN permschem.[name]       -- Schemas
                            WHEN 4 THEN imp.[name]             -- Impersonations
                            ELSE OBJECT_NAME(perm.[major_id])  -- General objects
                        END,
         [ColumnName] = col.[name]
     FROM
         --Role/member associations
         sys.database_role_members          AS members
         --Roles
         JOIN      sys.database_principals  AS roleprinc ON roleprinc.[principal_id] = members.[role_principal_id]
         --Role members (database users)
         JOIN      sys.database_principals  AS membprinc ON membprinc.[principal_id] = members.[member_principal_id]
         --Login accounts
         LEFT JOIN sys.server_principals    AS ulogin    ON ulogin.[sid] = membprinc.[sid]
         --Permissions
         LEFT JOIN sys.database_permissions AS perm      ON perm.[grantee_principal_id] = roleprinc.[principal_id]
         LEFT JOIN sys.schemas              AS permschem ON permschem.[schema_id] = perm.[major_id]
         LEFT JOIN sys.objects              AS obj       ON obj.[object_id] = perm.[major_id]
         LEFT JOIN sys.schemas              AS objschem  ON objschem.[schema_id] = obj.[schema_id]
         --Table columns
         LEFT JOIN sys.columns              AS col       ON col.[object_id] = perm.[major_id]
                                                            AND col.[column_id] = perm.[minor_id]
         --Impersonations
         LEFT JOIN sys.database_principals  AS imp       ON imp.[principal_id] = perm.[major_id]
     WHERE
         membprinc.[type] IN ('S','U','G','R')
         -- No need for these system accounts
         AND membprinc.[name] NOT IN ('sys', 'INFORMATION_SCHEMA')
       --  and objschem.[name]= 'call'

 UNION

     --3) List all access provisioned to the public role, which everyone gets by default
     SELECT
         [UserType]         = '{All Users}',
         [DatabaseUserName] = '{All Users}',
         [LoginName]        = '{All Users}',
         [Role]             = roleprinc.[name],
         [PermissionType]   = perm.[permission_name],
         [PermissionState]  = perm.[state_desc],
         [ObjectType] = CASE perm.[class]
                            WHEN 1 THEN obj.[type_desc]        -- Schema-contained objects
                            ELSE perm.[class_desc]             -- Higher-level objects
                        END,
         [Schema] = objschem.[name],
         [ObjectName] = CASE perm.[class]
                            WHEN 3 THEN permschem.[name]       -- Schemas
                            WHEN 4 THEN imp.[name]             -- Impersonations
                            ELSE OBJECT_NAME(perm.[major_id])  -- General objects
                        END,
         [ColumnName] = col.[name]
     FROM
         --Roles
         sys.database_principals            AS roleprinc
         --Role permissions
         LEFT JOIN sys.database_permissions AS perm      ON perm.[grantee_principal_id] = roleprinc.[principal_id]
         LEFT JOIN sys.schemas              AS permschem ON permschem.[schema_id] = perm.[major_id]
         --All objects
         JOIN      sys.objects              AS obj       ON obj.[object_id] = perm.[major_id]
         LEFT JOIN sys.schemas              AS objschem  ON objschem.[schema_id] = obj.[schema_id]
         --Table columns
         LEFT JOIN sys.columns              AS col       ON col.[object_id] = perm.[major_id]
                                                            AND col.[column_id] = perm.[minor_id]
         --Impersonations
         LEFT JOIN sys.database_principals  AS imp       ON imp.[principal_id] = perm.[major_id]
     WHERE
       roleprinc.[type] = 'R'
         AND roleprinc.[name] = 'public'
         AND obj.[is_ms_shipped] = 0 and 
         roleprinc.[name] <> 'Null' and objschem.[name] = 'call'  -- list a particular schema doesn't work.

 ORDER BY
 [Role] desc,
     [UserType],
     [DatabaseUserName],
     [LoginName],
     [Schema],
     [ObjectName],
     [ColumnName],
     [PermissionType],
     [PermissionState],
     [ObjectType] 
    

Select allOpen in new window

you can see I added:

1)     WHEN 'R' THEN 'Database Role'
2)        princ.[type] IN ('S','U','G','R')
3)      --2) List all access provisioned to a SQL user or Windows user/group through a database or application role
     SELECT
         [UserType] = CASE membprinc.[type]
                          WHEN 'S' THEN 'SQL User'
                          WHEN 'U' THEN 'Windows User'
                          WHEN 'G' THEN 'Windows Group'
                          WHEN 'R' THEN 'Database Role'
4)         LEFT JOIN sys.database_principals  AS imp       ON imp.[principal_id] = perm.[major_id]
     WHERE
         membprinc.[type] IN ('S','U','G','R')

and the problem I found out is :

when I use this as a condition:

   WHERE
  roleprinc.[type] = 'R'
         AND roleprinc.[name] = 'public'
         AND obj.[is_ms_shipped] = 0 and  
         roleprinc.[name] <> 'Null' and objschem.[name] = 'call'

Select allOpen in new window

schema other than 'call' and Null still show out !

we have 3 x script there:

--Gets list of users with server access and roles assigned
SELECT u.name,r.name,m.role_principal_id,m.member_principal_id,u.type,u.type_desc
  FROM sys.server_role_members AS m
  INNER JOIN sys.server_principals AS r
  ON m.role_principal_id = r.principal_id
  INNER JOIN sys.server_principals AS u
  ON u.principal_id = m.member_principal_id 
  order by u.name

Select allOpen in new window

and

DECLARE @DBuser_role_table TABLE (user_nm VARCHAR(200), role_group VARCHAR(250), role_id int, member_principal_id int,user_type char(1), type_desc VARCHAR(100), is_fixed_role bit )
DECLARE @DBuser_inherit_role_table TABLE (user_nm VARCHAR(200), role_group VARCHAR(250), role_id int, member_principal_id int,user_type char(1), type_desc VARCHAR(100), is_fixed_role bit )


INSERT @DBuser_role_table
SELECT u.name,r.name,m.role_principal_id,m.member_principal_id,u.type,u.type_desc,r.is_fixed_role
  FROM sys.database_role_members AS m
  INNER JOIN sys.database_principals AS r
  ON m.role_principal_id = r.principal_id
  INNER JOIN sys.database_principals AS u
  ON u.principal_id = m.member_principal_id order by u.name 




INSERT @DBuser_inherit_role_table
SELECT u.name,r.name,m.role_principal_id,m.member_principal_id,u.type,u.type_desc,r.is_fixed_role
  FROM sys.database_role_members AS m
  INNER JOIN sys.database_principals AS r
  ON m.role_principal_id = r.principal_id
  INNER JOIN sys.database_principals AS u
  ON u.principal_id = m.member_principal_id 
where m.member_principal_id in (SELECT distinct m.role_principal_id
  FROM sys.database_role_members AS m
  INNER JOIN sys.database_principals AS r
  ON m.role_principal_id = r.principal_id
  INNER JOIN sys.database_principals AS u
  ON u.principal_id = m.member_principal_id) 

--SELECT * FROM @DBuser_role_table order by role_id
--SELECT * FROM @DBuser_inherit_role_table order by role_id
  

--select u.user_nm,u.role_group,u.role_id,u.member_principal_id,u.user_type,u.type_desc,isnull(i.role_group,'') as inherit_role_group,isnull(i.role_id,'') as inherit_role_id,isnull(i.member_principal_id,'') as inherit_member_principal_id,isnull(i.type_desc,'') as inherit_type_desc from @DBuser_role_table u
select u.user_nm,u.role_group,u.type_desc,isnull(i.role_group,'') as inherit_role_group,isnull(i.type_desc,'') as inherit_type_desc from @DBuser_role_table u
left outer join @DBuser_inherit_role_table i
on i.member_principal_id=u.role_id
where u.type_desc <> 'DATABASE_ROLE'

Select allOpen in new window

and

 

--Gets list of roles and objects and permissions for each
SELECT DISTINCT rp.name as role, 
                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' 
--        rp.name='BusinessAnalysts'
ORDER  BY rp.name, 
          rp.type_desc, 
          pm.class_desc

Select allOpen in new window

do you think there are sth missing ?

hi, can you one off script show server level role too ?

tks.