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