How to Check SQL Server Security Part 2

In a previous post we started reviewing SQL Server security at the server level by looking at Logins. Is that enough though? It definitely isn’t! We need to look at the users at the database level as well. So, let’s do that.

 

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.”

This procedure has a lot of flexibility. If you want to focus on the security of a specific database, then use the @DBName parameter as in the first example below.

exec DBAUtility.dbo.sp_dbPermissions @DbName = 'CollegeFootball';

/*This option users a cursor to loop through all the databases on the instance*/
exec DBAUtility.dbo.sp_dbPermissions @DbName = 'ALL';

 

Notice the result set provides:

  • individual database principal names
  • a principal name description so you can see if this is a SQL login or Windows login
  • drop and create scripts

 

KennethFisher_sp_dbPermissions_DBName

KennethFisher_sp_dbPermissions_DBName_FirstResultSet

 

You also can pass in a database role you’re interested in using the @Role parameter. For instance you might want to see everything related to the db_owner role.

 

 

Next Steps To Take 

 

 

  1.  Download the latest script here and explore the available parameters.
  2. If you have questions or comments, leave me a comment on this post, or message me on LinkedIn or Twitter.
  3. Check out my Services page and set up a call with me via my Calendly so we can discuss the problems you’re having in your environment and how I can help. 

 

>