Custom SSMS Reports in SP2 - Database Permissions

Once of the great new enhancements in Management Studio in SP2 is the ability for users to create their own reports. There's a nice write up with details of the builtin parameters (and some of the limitations) here. One of the views I miss most from Enterprise Manager is being able to select a database user or role and see at a glance all the permissions that they have.

SP2 has actually made some improvements in this area in that when you select a user or role it will now load up all the objects that have explicit permissions into the top window of the permissions dialog (previously you had to select them manually using a rather clumsy interface) however you still have to select an individual object to see all permissions (e.g. need to select a table to see if the user has just select permissions or insert/update/delete as well).

So the first report I thought of was a "matrix" of all objects and common permissions for a specific user or role that shoudl cover 99% of scenarios. This is attached to this post as Database Permissions.zip

To use the report, extract it somewhere locally on your PC (default location is My Documents>SQL Server Management Studio>Custom Reports) than in Management Studio, browse to the Security folder under a database and right click on a user or database role and choose Reports>Custom Reports. Select the Database Permissions.rdl file and it will run the report (say yes to the warning dialog). The good thing is that SSMS will remember which reports are associated with which object types so from now on the report will be available under Custom Reports without having to browse for it. Hope it helps! A screenshot of an example report can be seen here

Published 21 November 2006 18:28 by sqldbatips
Filed under:

Comments

22 November 2006 13:54 by sqldbatips.com blog

# Custom SSMS Reports in SP2 - Enterprise Manager Taskpad View

This is probably one of the most requested reports for Management Studio, a simple overview of the total

23 November 2006 00:49 by SimonS SQL Server Stuff

# Taskpad view in SQL2005

Do you want the taskpad view in Management Studio then you need to download Jaspers custom report which

23 November 2006 00:49 by SimonS' SQL Server Stuff

# Taskpad view in SQL2005

Do you want the taskpad view in Management Studio then you need to download Jaspers custom report...

23 November 2006 00:52 by SimonS SQL Server Stuff

# Role based Permissions view in Managent Studio

Do you want the permission view that is avialble in Enterprise Manager for roles well you need to download

23 November 2006 00:52 by SimonS' SQL Server Stuff

# Role based Permissions view in Managent Studio

Do you want the permission view that is avialble in Enterprise Manager for roles well you need...

29 November 2006 00:24 by sqldbatips.com blog

# Custom SSMS Reports in SP2 - Enterprise Manager Taskpad View

This is probably one of the most requested reports for Management Studio, a simple overview of the total

20 December 2006 01:17 by SQL Server Manageability Team Blog

# SQL Server 2005 Service Pack 2 - December CTP

Before I go on vacation, I'd like to send a quick thank you to the community for giving us their thoughts on the November CTP of SP2. There were many thousands of downloads and a lot of people have started posting their suggestions and bug reports on

20 December 2006 01:18 by SQL Server Manageability Team Blog

# SQL Server 2005 Service Pack 2 - December CTP

Before I go on vacation, I'd like to send a quick thank you to the community for giving us their thoughts on the November CTP of SP2. There were many thousands of downloads and a lot of people have started posting their suggestions and bug reports on

05 June 2007 14:59 by SQL Server - Christian Robert

# SQL Server 2005 : Service Pack 2, quelques rapports personnalisés pour SSMS

Bon mis à part une matinée mouvementée (je ne reviendrais pas sur les détails de celle-ci), voici un

22 January 2009 03:33 by View Permissions by User | keyongtech

# View Permissions by User | keyongtech

Pingback from  View Permissions by User | keyongtech