A few years I was troubleshooting an issue with share and NTFS permissions

and I ran across a nice little utility called DumpSec from SomarSoft which

generates reports on permissions and user accounts for an Windows based system.

You can obtain DumpSec from SomarSoft (http://www.systemtools.com/somarsoft/)

it’s a utility for auditing and reporting of NTFS and share

security, users and groups, audit settings, registry permissions, and user

rights assignment.

After using DumpSec I thought it would great to have a similar utility for SQL

Server which would report SQL Server security information. So I created

“DumpSQLSec” which generates reports on:

• Permissions for SQL Server Objects across multiple databases
• DB Users across multiple database with optional role membership
• DB Roles across multiple database with optional built-in roles and role

  members

• DB Privileges across multiple database
• Server Roles with optional server role Logins
• Server Logins with optional server roles and database access

“DumpSQLSec” has been tested against SQL 7.0 and SQL 2000 and strictly uses

SQL-DMO to get security information. I’ve found SQL-DMO a little easier to work

with than trying to wade through system tables for similar information. To keep

things simple “DumpSQLSec” requires the SQL Server client tools and Microsoft

Excel to be installed. All reports are outputted to Microsoft Excel and the

application uses SQL-DMO libraries which are installed with the client tools.

To use DumpSQLSec first follow the instructions in the readme file for

installing DumpSQLSec. Once installed launch DumpSQLSec and select a SQL Server

by selecting Report, and Select SQL Server. The available servers list is

populated from the SQL Servers locally registered in Enterprise Manager. You can

also type in a SQL Server. A few notes about the report output: Explicit

permissions for SQL objects are recorded as an “X”. Explicit deny permissions

are recorded as an “O” and SQL

objects without any explicit permission assigned will have NULLs under SELECT,

UPDATE, INSERT, DELETE, EXEC or DRI

Over the last three years of I’ve used “DumpSQLSec” to clean up messy

permissions on a number of databases. I’ve also used “DumpSQLSec” to generate

reports for auditors.

Included in the download are two zip files; one with a

compiled exe and the other the VB6 source

code. The code could have been written a little cleaner (reuse similar

functions instead of copying and pasting and a better report output among other

things) however it’s not bad coding for a full DBA and it’s free -- no strings

attached! On my long list of things to do is to cleanup the code and rewrite it

in .NET, and add some additional functionality. For one thing I’d like to see

effective permissions for users or roles (similar in concept to Windows XP’s

ability to list effective permissions under the security, advanced, effective

permissions tab).

Hopefully you’ll find this utility as useful as I have. Let me know what you

think.