User_Permissions 061324a1-f64c-413e-9e6a-061e9a2bfbdb 1in 1in String = " All" Principal Name AvailableUsers ID name true 8.5in true textbox1 1 8in true 0.3in = "User Permissions - " + Parameters!ObjectName.Value User_Permissions0.3in
principal_name 15 true =Fields!principal_name.Value textbox11 14 true textbox12 13 true textbox13 12 true textbox14 11 true textbox15 10 true textbox16 9 true textbox17 8 true 0.21in
=Fields!principal_name.Value Ascending =Fields!principal_name.Value
textbox3 7 true login 6 true =Fields!login.Value role_name 5 true =Fields!role_name.Value principal_type_desc 4 true =Fields!principal_type_desc.Value class_desc 3 true =Fields!class_desc.Value object_name 2 true =Fields!object_name.Value permission_name 1 true =Fields!permission_name.Value permission_state_desc true =Fields!permission_state_desc.Value 0.21in
textbox2 23 true Principal Name textbox4 22 true Login Name textbox5 21 true Role Name textbox6 20 true Principal Type textbox7 19 true Object Type textbox8 18 true Object Name textbox9 17 true Permission Name textbox10 16 true Permission State 0.22in true
2in 1.75in 1.625in 1.625in 1.75in 3.5in 1.125in 1.75in
0.94in d50cc08b-8fe1-4099-984b-5a3e89fb3500 1in true WITH perms_cte(principal_name,principal_id, principal_type_desc,class_desc, [object_name], permission_name, permission_state_desc, login ) as ( select USER_NAME(p.grantee_principal_id) AS principal_name, dp.principal_id, dp.type_desc AS principal_type_desc, p.class_desc, case when p.class_desc = 'DATABASE' then db_name() when p.class_desc = 'SCHEMA' then (select name from sys.schemas s where s.schema_id = p.major_id) when p.class_desc = 'SYMMETRIC_KEYS' then (select name from sys.symmetric_keys sk where sk.symmetric_key_id = p.major_id) when p.class_desc = 'CERTIFICATE' then (select name from sys.certificates c where c.certificate_id = p.major_id) when p.class_desc = 'ASYMMETRIC_KEY' then (select name from sys.asymmetric_keys ak where ak.asymmetric_key_id = p.major_id) else OBJECT_NAME(p.major_id) end AS [object_name], p.permission_name, p.state_desc AS permission_state_desc, sp.name as login from sys.database_permissions p left JOIN sys.database_principals dp on p.grantee_principal_id = dp.principal_id left Join sys.server_principals sp on dp.sid = sp.sid ) -- Users SELECT p.principal_name , login , cast('<granted explicit>' as sysname) as role_name , p.principal_type_desc , p.class_desc , p.[object_name] , p.permission_name , p.permission_state_desc FROM perms_cte p where p.principal_name = @ObjectName or @ObjectName = ' All' UNION -- Role Members SELECT rm.member_principal_name , rm.login , rm.role_name , rm.principal_type_desc , p.class_desc , p.[object_name] , p.permission_name , coalesce(p.permission_state_desc, '<Member of fixed database-role:>') 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 , sp.name as login from sys.database_role_members rm INNER JOIN sys.database_principals dp ON rm.member_principal_id = dp.principal_id left Join sys.server_principals sp on dp.sid = sp.sid ) rm ON rm.role_principal_id = p.principal_id where rm.member_principal_name = @ObjectName or @ObjectName = ' All' order by 2, 4, 1, 8, 5, 6 =Parameters!ObjectName.Value User_Permissions System.String principal_name System.String login System.String role_name System.String principal_type_desc System.String class_desc System.String object_name System.String permission_name System.String permission_state_desc true select ' All' as name, ' All' union select Case when issqlrole = 1 Then 'Role - ' + name When issqlrole = 0 Then 'User - ' + name End as Name, Name as Real_Name from sys.sysusers order by name User_Permissions System.String name System.String 15.125in 11in en-US 1in