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.
Tag Archives: SQL Server Security
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.
CORRECTION: This is for CTP4 (Community Technology Preview) not RTM (Release to Manufacturing). Therefore, you cannot install this if you are running a full version of SQL Server 2012. Sorry for the confusion, the original article I read did not mention anything about it being for CTP.
Fresh off the tree and ripe for picking…
Link for downloading SQL Server 2012 CTP4 Service Pack 1:
Every wonder who all your orphaned SQL Server users are on your server? Just run the script below and copy the results of the query into a new query window then execute them. Make sure you keep the spacing and the quotes as they are so that the Dynamic SQL generated is free of Syntax errors.
SELECT 'USE '+ name+ ' EXEC sp_change_users_login ''Report'' '
WHERE name not in ('master','msdb','model','distribution','tempdb')
ORDER BY name
But wait a minute, I only know which orphaned users are on my server. I want to actually fix them! Ok, ok relax, I have the solution for that just keep reading.
Professional Association for SQL Server Presentation
Topic: SQL Server Security Architecture Decomposed
Presenter: Ayman El-Ghazali
Abstract: Security can be difficult to understand when first approaching SQL Server due to the many layers. In this webinar we will take a top down look at SQL Server Security and then “peel” its layers one by one to understand how it works. This webinar will help those who wish to understand security layers and concepts better with in SQL Server. We will cover how to properly manage individual and group access to your SQL Instance, Databases, and many other securables. Please join us on this journey from your server’s network port all the way to your tables, stored procedures, and functions.
June 27, 2012 – SQL Server Security Architecture Decomposed. Click here to start streaming.
Slides and Code for all Presentations can be found here