Press "Enter" to skip to content

Procedures for Reviewing SQL Server Security

Lee Markum continues a series on SQL Server security. Part 2 looks at sp_DBPermissions:

Again, the internet is awash with scripts and options so let me give you a place to start.

Kenneth Fisher’s sp_DBPermissions

From the comments in the stored procedure, “This stored procedure returns 3 data sets. The first data set is the list of database principals, the second is role membership, and the third is object and database level permissions.”

Part 3 reviews security options in sp_Blitz:

Open source provides a lot of fantastic scripts and software. One of the more popular ones is the First Responder Kit. In that collection of scripts is sp_Blitz. This script is great because it provides a lot of information. The script results can also be problematic, because it provides so much information. On my little SQL Server I use for testing scripts and writing demos, sp_Blitz returned 68 rows across 8 priority levels or categories of issues. Again, wonderful if you want to see all the things! But, what if you’re trying to focus on a particular type of issue, like security? What do you do then?