sp_Blitz Result: User with Elevated Database Permissions

Inside a database, users can be set up in the roles db_owner, db_accessadmin, db_securityadmin, and db_ddladmin.  The Books Online page on database-level roles explains the permissions for these roles, all of which involve more than just reading and writing data.  Some of these can get you fired if somebody even just clicks around too much in SSMS – it’s way too easy to rename an object accidentally.

This part of our SQL Server sp_Blitz script checks the sysmembers table in each database looking for users in elevated roles.

To Fix the Problem

Talk to the users listed and find out if they really need to change database objects or security.  If not, remove them from the roles.  In SQL Server Management Studio, you can go into Security, Logins, and right-click on a login.  Go to User Mapping and you can see their roles for each database.  Unchecking them from the roles for each database will take them out immediately – but just make sure they’ve got SOME kind of access so they can still do their queries.  We’re a big fan of db_datareader and db_datawriter – those two roles give people rights to read and write to any table in the database.  (It’s not nearly as good as doing fine-grained permissions for just the tables they need, but it’s way better than making them db_owner.)

Return to sp_Blitz or Ask Us Questions