Finally the epic conclusion to my three part series about listing server and database roles. In this post I will show you how to create SSRS report to use for regular tracking or auditing purposes. This is a much easier and cleaner way of looking at the data and you can provide it to other team members or even your manager.
You can read Part 1 here if you haven’t yet.
You can read Part 2 here if you haven’t yet.
***Important – The following report was designed with SSRS 2008. If you are using SSRS 2005, some of the steps will be done differently (specifically the setup for the report parameters). SSRS for 2012 should work the same since SQL Server Data Tools have a similar layout as Visual Studio 2008***
On to the fun stuff. For this final part you will need to have one of the following configurations to create reports:
1. Business Intelligence Development Studio (BIDS) free install with SQL Server
2. SQL Server Data Tools the same as BIDS but for SQL 2012
3. BIDS and Visual Studio – Visual studio alone will not have the BIDS components
Start a new Reporting Services project or open an existing one if you wish to add these reports to an already existing project. I would suggest using the Project Wizard because it is easier to arrange the columns and rows of the matrix.
Alright now let’s create our Database Level permissions report.
1. Click next on the welcome screen of the create report wizard. Create a new data source or use an existing one. Obviously it will be the server and database you used to create your tables from part 1 of this blog series. Click next.
2. Use the following code for your query and then click next again.
SELECT ServerName,DatabaseName,UserRole, UserName
FROM dbo.DatabasePermissions
WHERE servername in (@servername)
and databasename in (@databasename)
ORDER BY ServerName,DatabaseName,UserRole, UserName
3. Select Matrix for the report type and click next.
4. Add the column ServerName to the page section and UserName to the Details section. Put UserrRole in the Columns section and DatabaseName in the Rows section. You can also swap them around if you wish. See the screen shot below for guidance.
Click Next when done
5. Select your report style press next. Name your report and click Finish.
6. Create a new dataset for your report named servername by right clicking on Datasets in the left window (labeled Report Data). Select “Use a dataset embedded in my report” and select the datasource that you previously created. Use the following query:
SELECT DISTINCT servername
FROM databasepermissions
ORDER BY ServerName
7. Repeat this process to create a third dataset called databasename and use the following query for it:
SELECT DISTINCT databasename
FROM databasepermissions
WHERE servername in (@servername)
ORDER BY databasename
8. Alright now to fill in those fancy parameters we created. In the left pane called Report Data there is a section for parameters and it should have two parameters listed (servername and databasename). If not, then you will need to create them by right clicking on the parameters folder and selecting “Add Parameter.” THE NAMES ARE CASE SENSITIVE.
9. When you create or edit the parameters properties sheet should pop-up (double click on the parameter to bring this up). Let us start with the servername parameter; at this point you should start creating it or editing it. Under the general tab select the option “Allow multiple values”
10. Go to the next tab on the left labeled “Available Values” and use the radio button to select “Get values from a query.” Set your configuration as follows:
11. Optionally, you can repeat the previous step for the “Default Values” tab. Click OK when done.
12. Now for the final step. Repeat the same process to create/edit the databasename parameter. This time use the dataset named databasename. Again, you can setup the default values if you would like. The following screen shot is for guidance.
And there you have it, one fancy looking report that you can use over and over again and share with others. Make sure you setup proper permissions when you deploy this to your report server.
Now on to the Server Level Permissions report. It is a lot easier and has fewer components than the previous report we created.
1. Create a new report in the same project, preferably use the report wizard.
2. Use the following code for your query and then click next again.
SELECT UserName, UserRole, ServerName
FROM ServerPermissions
ORDER BY ServerName
3. Select Matrix for the report type and click next.
4. Add UserName to the Details section. Put UserRole in the Columns section and ServerName in the Rows section. You can also swap them around if you wish. See the screen shot for above from step 4 of the Database Level Report for guidance. Note: Columns will be different
5. Select your report style press next. Name your report and click Finish.
Now you have two useful reports that you can use to audit security across your servers. It saved me hours of time, hopefully it will help you too.
The SQL Pro 