Earlier this week I decided that I wanted to generate a report that shows me how the security is set up on my databases. I’m mainly intersted in my database roles and how they’re set up, and logins assigned to the database. So I did some snooping around and generated 3 queries for myself.

T-SQL
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
select
    dpr.name,
    dpr.principal_id,
    dpr.type,
    dpr.type_desc,
    dp.class,
    dp.class_desc,
    dp.major_id,
    dp.minor_id,
    dp.grantee_principal_id,
    dp.permission_name,
    case dp.state
        when 'g' then 'granted'
        when 'd' then 'denied'
        else 'n/a'
    end AS [state],
    ao.name as ObjectName,
    sc.name as SchemaName,
    case class_desc
        when 'OBJECT_OR_COLUMN' then ao.name
        when 'SCHEMA' then sc.name
    end AS PermissionObject
from sys.database_principals dpr
    left outer join sys.database_permissions dp
        on dpr.principal_id=dp.grantee_principal_id
    left outer join sys.all_objects ao
        on dp.major_id=ao.object_id
    left outer join sys.schemas sc
        on dp.major_id=sc.schema_id
where dpr.type='r'
    and class_desc>''
    and dpr.principal_id>0
order by name
select
	dpr.name,
	dpr.principal_id,
	dpr.type,
	dpr.type_desc,
	dp.class,
	dp.class_desc,
	dp.major_id,
	dp.minor_id,
	dp.grantee_principal_id,
	dp.permission_name,
	case dp.state
		when 'g' then 'granted'
		when 'd' then 'denied'
		else 'n/a'
	end AS [state],
	ao.name as ObjectName,
	sc.name as SchemaName,
	case class_desc
		when 'OBJECT_OR_COLUMN' then ao.name
		when 'SCHEMA' then sc.name
	end AS PermissionObject
from sys.database_principals dpr
	left outer join sys.database_permissions dp
		on dpr.principal_id=dp.grantee_principal_id
	left outer join sys.all_objects ao
		on dp.major_id=ao.object_id
	left outer join sys.schemas sc
		on dp.major_id=sc.schema_id
where dpr.type='r'
	and class_desc>''
	and dpr.principal_id>0
order by name

This query displays all rights explicitly granted and denied in the database to database roles.

In this query, name is the name of the database role. Type and type_desc describe what the grantee principal id is. class and class_desc describes what is having a right granted to it. Permission_name shows the permission, and state shows if that permission is being granted or denied. Object name is the object that is having a right granted, schema name is the schema name that’s being granted a right.

T-SQL
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
select
    dpr.name,
    dpr.principal_id,
    dpr.type,
    dpr.type_desc,
    dp.class,
    dp.class_desc,
    dp.major_id,
    dp.minor_id,
    dp.grantee_principal_id,
    dp.permission_name,
    case dp.state
        when 'g' then 'granted'
        when 'd' then 'denied'
        else 'n/a'
    end AS [state],
    ao.name as ObjectName,
    sc.name as SchemaName,
    case class_desc
        when 'OBJECT_OR_COLUMN' then ao.name
        when 'SCHEMA' then sc.name
    end AS PermissionObject
from sys.database_principals dpr
    left outer join sys.database_permissions dp
        on dpr.principal_id=dp.grantee_principal_id
    left outer join sys.all_objects ao
        on dp.major_id=ao.object_id
    left outer join sys.schemas sc
        on dp.major_id=sc.schema_id
where dpr.type in
(
    'S',
    'G',
    'U'
)
select
	dpr.name,
	dpr.principal_id,
	dpr.type,
	dpr.type_desc,
	dp.class,
	dp.class_desc,
	dp.major_id,
	dp.minor_id,
	dp.grantee_principal_id,
	dp.permission_name,
	case dp.state
		when 'g' then 'granted'
		when 'd' then 'denied'
		else 'n/a'
	end AS [state],
	ao.name as ObjectName,
	sc.name as SchemaName,
	case class_desc
		when 'OBJECT_OR_COLUMN' then ao.name
		when 'SCHEMA' then sc.name
	end AS PermissionObject
from sys.database_principals dpr
	left outer join sys.database_permissions dp
		on dpr.principal_id=dp.grantee_principal_id
	left outer join sys.all_objects ao
		on dp.major_id=ao.object_id
	left outer join sys.schemas sc
		on dp.major_id=sc.schema_id
where dpr.type in
(
	'S',
	'G',
	'U'
)

This query shows all of the rights explicitly granted/denied to logins.

T-SQL
1
2
3
4
5
6
7
8
SELECT 
    dpm.name as Member,
    dpg.name as Grp
  FROM ardb.[sys].[sysmembers] sm
    left outer join sys.database_principals dpm
        on sm.memberuid=dpm.principal_id
    left outer join sys.database_principals dpg
        on sm.groupuid=dpg.principal_id
SELECT 
	dpm.name as Member,
	dpg.name as Grp
  FROM ardb.[sys].[sysmembers] sm
	left outer join sys.database_principals dpm
		on sm.memberuid=dpm.principal_id
	left outer join sys.database_principals dpg
		on sm.groupuid=dpg.principal_id

This query shows all of the database roles and all of the logins assigned to the roles. Grp is the database role and Member is the login name.

Now, these three queries gave me a good starting point, but to run these you have to go to each database. And if i want to query different servers (i’ve got 3, a dev, a test, and a prod) to make sure they’re similer, i’d have to connect to each server. So, I created a couple of stored procedures and threw those into a reporting services report. Now, i can go to the report, query a sever and a database, and then compare it to another server and database.

here’s the script to create the sp’s that i made.

T-SQL
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
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
-- =============================================
-- Author:      David Forck DF
-- Create date: 01Oct10
-- Description: Report to show database role membership
-- =============================================
CREATE PROCEDURE [dbo].[RoleMembership]
    -- Add the parameters for the stored procedure here
    @servername varchar(max),
    @databasename nvarchar(max)
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;
 
    -- Insert statements for procedure here
 
 
 
 
declare @sql varchar(max), 
    @database varchar(max), 
    @server varchar(max), 
    @string varchar(max)
 
 
 
select @server= name from sys.servers where name=@servername
 
select @database= name from sys.databases where name=@databasename
 
set @string=@server+'.'+@database
 
 
 
set @sql='
SELECT 
    dpm.name as Member,
    dpg.name as Grp
  FROM ' + @string + '.[sys].[sysmembers] sm
    left outer join ' + @string + '.sys.database_principals dpm
        on sm.memberuid=dpm.principal_id
    left outer join ' + @string + '.sys.database_principals dpg
        on sm.groupuid=dpg.principal_id
'
 
--print @databasename
--print @sql
 
exec(@sql)
 
 
 
 
 
 
END
 
 
GO
 
 
-- =============================================
-- Author:      David Forck DF
-- Create date: 01Oct10
-- Description: security report for logins
-- =============================================
CREATE PROCEDURE [dbo].[LoginSecurity]
    -- Add the parameters for the stored procedure here
    @servername varchar(max),
    @databasename nvarchar(max)
    
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;
 
    -- Insert statements for procedure here
 
 
 
declare @sql varchar(max), 
    @database varchar(max), 
    @server varchar(max), 
    @string varchar(max)
 
select @server= name from sys.servers where name=@servername
 
select @database= name from sys.databases where name=@databasename
 
set @string=@server+'.'+@database
 
 
set @sql='
select
    dpr.name,
    dpr.principal_id,
    dpr.type,
    dpr.type_desc,
    dp.class,
    dp.class_desc,
    dp.major_id,
    dp.minor_id,
    dp.grantee_principal_id,
    dp.permission_name,
    case dp.state
        when ''g'' then ''granted''
        when ''d'' then ''denied''
        else ''n/a''
    end AS [state],
    ao.name as ObjectName,
    sc.name as SchemaName,
    case class_desc
        when ''OBJECT_OR_COLUMN'' then ao.name
        when ''SCHEMA'' then sc.name
    end AS PermissionObject
from ' + @string + '.sys.database_principals dpr
    left outer join ' + @string + '.sys.database_permissions dp
        on dpr.principal_id=dp.grantee_principal_id
    left outer join ' + @string + '.sys.all_objects ao
        on dp.major_id=ao.object_id
    left outer join ' + @string + '.sys.schemas sc
        on dp.major_id=sc.schema_id
where dpr.type in
(
    ''S'',
    ''G'',
    ''U''
)
'
 
--print @databasename
--print @sql
 
exec(@sql)
 
END
 
 
 
 
-- =============================================
-- Author:      David Forck DF
-- Create date: 01Oct10
-- Description: Stored procedure that generates a security report per database for database roles
-- =============================================
CREATE PROCEDURE [dbo].[DatabaseRoleSecurity]
    -- Add the parameters for the stored procedure here
    @servername varchar(max),
    @databasename varchar(max)
    
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;
 
    -- Insert statements for procedure here
 
 
declare @sql varchar(max), 
    @database varchar(max), 
    @server varchar(max), 
    @string varchar(max)
 
 
select @server= name from sys.servers where name=@servername
 
select @database= name from sys.databases where name=@databasename
 
set @string=@server+'.'+@database
 
 
set @sql='
 
select
    dpr.name,
    dpr.principal_id,
    dpr.type,
    dpr.type_desc,
    dp.class,
    dp.class_desc,
    dp.major_id,
    dp.minor_id,
    dp.grantee_principal_id,
    dp.permission_name,
    case dp.state
        when ''g'' then ''granted''
        when ''d'' then ''denied''
        else ''n/a''
    end AS [state],
    ao.name as ObjectName,
    sc.name as SchemaName,
    case class_desc
        when ''OBJECT_OR_COLUMN'' then ao.name
        when ''SCHEMA'' then sc.name
    end AS PermissionObject
from ' + @string + '.sys.database_principals dpr
    left outer join ' + @string + '.sys.database_permissions dp
        on dpr.principal_id=dp.grantee_principal_id
    left outer join ' + @string + '.sys.all_objects ao
        on dp.major_id=ao.object_id
    left outer join ' + @string + '.sys.schemas sc
        on dp.major_id=sc.schema_id
where dpr.type=''r''
    and class_desc>''''
    and dpr.principal_id>0
order by name'
    
 
--print @database
--print @server
--print @string
--print @sql
 
exec(@sql)
 
 
END
 
 
GO
-- =============================================
-- Author:		David Forck DF
-- Create date: 01Oct10
-- Description:	Report to show database role membership
-- =============================================
CREATE PROCEDURE [dbo].[RoleMembership]
	-- Add the parameters for the stored procedure here
	@servername varchar(max),
	@databasename nvarchar(max)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here




declare @sql varchar(max), 
	@database varchar(max), 
	@server varchar(max), 
	@string varchar(max)



select @server= name from sys.servers where name=@servername

select @database= name from sys.databases where name=@databasename

set @string=@server+'.'+@database



set @sql='
SELECT 
	dpm.name as Member,
	dpg.name as Grp
  FROM ' + @string + '.[sys].[sysmembers] sm
	left outer join ' + @string + '.sys.database_principals dpm
		on sm.memberuid=dpm.principal_id
	left outer join ' + @string + '.sys.database_principals dpg
		on sm.groupuid=dpg.principal_id
'

--print @databasename
--print @sql

exec(@sql)






END


GO


-- =============================================
-- Author:		David Forck DF
-- Create date: 01Oct10
-- Description:	security report for logins
-- =============================================
CREATE PROCEDURE [dbo].[LoginSecurity]
	-- Add the parameters for the stored procedure here
	@servername varchar(max),
	@databasename nvarchar(max)
	
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here



declare @sql varchar(max), 
	@database varchar(max), 
	@server varchar(max), 
	@string varchar(max)

select @server= name from sys.servers where name=@servername

select @database= name from sys.databases where name=@databasename

set @string=@server+'.'+@database


set @sql='
select
	dpr.name,
	dpr.principal_id,
	dpr.type,
	dpr.type_desc,
	dp.class,
	dp.class_desc,
	dp.major_id,
	dp.minor_id,
	dp.grantee_principal_id,
	dp.permission_name,
	case dp.state
		when ''g'' then ''granted''
		when ''d'' then ''denied''
		else ''n/a''
	end AS [state],
	ao.name as ObjectName,
	sc.name as SchemaName,
	case class_desc
		when ''OBJECT_OR_COLUMN'' then ao.name
		when ''SCHEMA'' then sc.name
	end AS PermissionObject
from ' + @string + '.sys.database_principals dpr
	left outer join ' + @string + '.sys.database_permissions dp
		on dpr.principal_id=dp.grantee_principal_id
	left outer join ' + @string + '.sys.all_objects ao
		on dp.major_id=ao.object_id
	left outer join ' + @string + '.sys.schemas sc
		on dp.major_id=sc.schema_id
where dpr.type in
(
	''S'',
	''G'',
	''U''
)
'

--print @databasename
--print @sql

exec(@sql)

END




-- =============================================
-- Author:		David Forck DF
-- Create date: 01Oct10
-- Description:	Stored procedure that generates a security report per database for database roles
-- =============================================
CREATE PROCEDURE [dbo].[DatabaseRoleSecurity]
	-- Add the parameters for the stored procedure here
	@servername varchar(max),
	@databasename varchar(max)
	
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here


declare @sql varchar(max), 
	@database varchar(max), 
	@server varchar(max), 
	@string varchar(max)


select @server= name from sys.servers where name=@servername

select @database= name from sys.databases where name=@databasename

set @string=@server+'.'+@database


set @sql='

select
	dpr.name,
	dpr.principal_id,
	dpr.type,
	dpr.type_desc,
	dp.class,
	dp.class_desc,
	dp.major_id,
	dp.minor_id,
	dp.grantee_principal_id,
	dp.permission_name,
	case dp.state
		when ''g'' then ''granted''
		when ''d'' then ''denied''
		else ''n/a''
	end AS [state],
	ao.name as ObjectName,
	sc.name as SchemaName,
	case class_desc
		when ''OBJECT_OR_COLUMN'' then ao.name
		when ''SCHEMA'' then sc.name
	end AS PermissionObject
from ' + @string + '.sys.database_principals dpr
	left outer join ' + @string + '.sys.database_permissions dp
		on dpr.principal_id=dp.grantee_principal_id
	left outer join ' + @string + '.sys.all_objects ao
		on dp.major_id=ao.object_id
	left outer join ' + @string + '.sys.schemas sc
		on dp.major_id=sc.schema_id
where dpr.type=''r''
	and class_desc>''''
	and dpr.principal_id>0
order by name'
	

--print @database
--print @server
--print @string
--print @sql

exec(@sql)


END


GO

Now, if you get to looking, the verification of the server name and database is actually running on the server that’s hosting the sp’s. I could have spent some more time and changed this to do this dynamically, but most of my servers have a similer database list. So, keep that in mind if you use these.