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.
Monthly Archives: October 2012
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?