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

Read through the options below to find out if you need to reduce permissions and whether you’d like to make the change in place, or transition users to new logins.

Return to sp_Blitz or Ask Us Questions

---

How to Reduce User Permissions

Talk to the users listed and find out if they really need to change database objects or security. If everyone says they need to be able to change objects and drop tables, talk to business owners about the risks. Explain that just with built-in tools, it can be tricky to find out who dropped a table if you don’t catch it immediately.

Choose Lower Permissions

We’re big fans 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 wayyyy better than making them db_owner.)

Option 1: Edit Database Roles For an Existing User

In SQL Server Management Studio:

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

OPTION 2: Transition Users to New Custom Roles, or even New Logins

Take another look at these high permission users:

1. Are they using SQL Authentication when maybe they should be using more secure Windows Authentication?
2. Are you setting up permissions for individual logins when you could manage this more easily using Active Directory groups?

If you have multiple issues to fix, it’s not necessarily harder to address them all at once. Configure new logins with new permissions, and transition the users to the new logins. One benefit of this is that if anything goes wrong right away, it’s very easy to roll everything back: just re-enable their old login to get them going again, then troubleshoot the issue with the new login.