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_Permissions
0.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