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?