LessThanDot

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.

LTD Social Sitings

Note: Watch for social icons on posts by your favorite authors to follow their postings on these and other social sites.

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.

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.

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