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.
Category Archives: SQL Server BI
Listing server and database roles for all logins and users in SQL Server – Part 3 – Using SSRS Reports to Monitor and Fix security problems
Building upon the code in my previous post, I will be retrieving permissions information from remote servers that I have access to. This was a little more difficult to accomplish because security is different on different servers and I did not want to create Linked Servers with escalated permissions. Read only SQL users was an option, however there is no good way to make sure that these users will have read permissions on all current and future databases. It took a while to over come these challenges but I got it working; obviously since I have this blog post.
I was approached with a unique problem to solve; find out who has what permissions on my Database Servers. This is the first post in a three part series about finding all user permissions on your SQL Server instance. The next posts will build upon this and introduce a way to use the same queries to get permissions from remote servers. In the final post, I will create a report that will nicely display the results of all those queries making it easy to audit and fix security problems. The granularity I’m going for is mapping logins/users to their corresponding roles at the instance and database level.
It has been a long morning for me. It was supposed to be a standard run for our quarterly data aggregations but one of the data import SSIS packages kept failing. The problem was a conversion problem from varchar type to float and I kept getting the following error:
Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to float.
However, my data was numeric I checked it many times… or was it really 100% numeric?
One big question I have gotten often is “I need data, what kind of reports do you have?” It is a valid question from a user, especially with a server that may gave hundreds of reports. The solution? Create a Reports Catalog Report. I have provided the simple instructions and code to create this report in 15 minutes or less.
The first job I got out of college was what introduced me to SQL Server Reporting Services. Initially I thought the job was a less technical reporting analyst position with a focus on competitive/business intelligence.
I found myself loving the technical work more and more; my concentration was in DBMS in college so this just built upon that love. Although I don’t work with SSRS as much anymore, I still love going back and doing report development, maintaining the reporting environment, and looking at report usage stats.