Less Than Dot is a community of passionate IT professionals and enthusiasts dedicated to sharing technical knowledge, experience, and assistance. Inside you will find reference materials, interesting technical discussions, and expert tips and commentary. Once you register for an account you will have immediate access to the forums and all past articles and commentaries.

Your profile

Tools

Authors

Main Categories

Search

XML Feeds

RSS 2.0: Posts, Comments
Atom: Posts, Comments

What is RSS?

Google Ads

« The History of SQL Server Integration Services

Parsing the Address field to its individual components »

comments

Security Auditing a Database

by David Forck (thirster42) on Oct 08, 2010 in categories Microsoft SQL Server Admin, Microsoft SQL Server

Instapaper

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.

tsql

Line number Off | Hide | Select all

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.

tsql

Line number Off | Hide | Select all

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.

tsql

Line number Off | Hide | Select all

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.

tsql

Line number Off | Hide | Select all

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

About the Author

I have an A.S. in Computer Programming. I am currently a Database Developer (April 2008-Present) for a development group at a contract research lab, where we build in-house applications using Microsoft SQL Server (2005 and 2008) and ASP.Net 3.5. I have a part-time job (April 2007-Present) as a dba/db developer/web developer/designer. I use: SQL Server 2005/08/R2, HTML, CSS, PHP, and Java Script. Penguins are awesome, but I'm not a linux person. If you have any questions feel free to email me at dforck@gmail.com

Social Sitings

Article Views: 1636 views

Read More Posts by David Forck (thirster42)

Instapaper

3 comments

Comment from: SQLDenis [Member]

Are you saying that you don't run everything as sa with a '' password...what happened...burned by SQL Slammer?

10/08/10 @ 16:05

Comment from: David Forck (thirster42) [Member]

lmao @denis. no. all of our sa accounts have passwords. we still have things using it though.

10/08/10 @ 18:23

Comment from: FFGuitar [Member]

congrats on getting picked up by sqlserver central.

10/18/10 @ 07:59

Comment feed for this post

LessThanDot

Your profile

Tools

Tag cloud

Authors

Main Categories

Search

XML Feeds

Google Ads

Security Auditing a Database

About the Author

3 comments

Leave a comment

LessThanDot

LTD Social Sitings

Your profile

Tools

Tag cloud

Authors

Main Categories

Search

XML Feeds

Google Ads

Security Auditing a Database

About the Author

3 comments

Leave a comment