SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Dump SQL Permissions

By Chad Miller,

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.

Total article views: 17715 | Views in the last 30 days: 12
Related Articles

Reprting server -- permissions

Report server access permissions


Reporting on Activity across Multiple Servers using SQL Backup and SQL Multi Script

Rodney Landrum presents a creative solution for dynamic reporting across all of his SQL Servers, bas...


The Complete SQL Server 2005 Permissions Report

The script provides complete and comprehensive permission reports, on all SQL Server security hierar...


Views across a linked server

Views across a linked server


views across a linked server

views across a linked server

sql server 6.5    
sql server 7