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.
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.
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.
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.
-- =============================================
-- 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.
