Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
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: 17643 | Views in the last 30 days: 13
 
Related Articles
FORUM

Reprting server -- permissions

Report server access permissions

ARTICLE

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

SCRIPT

The Complete SQL Server 2005 Permissions Report

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

FORUM

Views across a linked server

Views across a linked server

FORUM

views across a linked server

views across a linked server

Tags
administration    
programming    
security    
sql server 6.5    
sql server 7    
sql-dmo    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones