1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
|
/*******************************************************
Some Basic Info on Database principals, permissions, explicit permissions, and if role, who is in this role currently
*******************************************************/
;with roleMembers as (
select
drm.role_principal_id
,dp.principal_id
,dp.name
from
sys.database_role_members drm
inner join sys.database_principals dp
on drm.member_principal_id = dp.principal_id
)
select
db_name()
,dp.name
,stuff((
select distinct
', ' + p.permission_name
from
sys.database_permissions p
where
dp.principal_id = p.grantee_principal_id
and p.major_id = 0
and p.state = 'G'
for xml path(''), type
).value('.', 'varchar(max)'), 1, 1, ''
) as general_permissions
,stuff((
select distinct
', ' + p.permission_name
from
sys.database_permissions p
where
dp.principal_id = p.grantee_principal_id
and p.major_id = 0
and p.state = 'D'
for xml path(''), type
).value('.', 'varchar(max)'), 1, 1, ''
) as deny_permissions
,stuff((
select distinct
', ' + p.permission_name + ' on ' + object_schema_name(p.major_id) + '.' + object_name(p.major_id)
from
sys.database_permissions p
where
dp.principal_id = p.grantee_principal_id
and p.major_id <> 0
for xml path(''), type
).value('.', 'varchar(max)'), 1, 1, ''
) as specific_permissions
,stuff((
select distinct
', ' + r.name
from
roleMembers r
where
r.role_principal_id = dp.principal_id
for xml path(''), type
).value('.', 'varchar(max)'), 1, 1, ''
) as current_active_members
from
sys.database_principals dp
order by
dp.name asc;
|
Webmentions
(No webmentions yet.)