5 Reasons Against Allowing db_owner Role Permissions

As someone who has had to implement independent software vendor-developed (aka "purchased") solutions with a foundation in SQL Server for over 15 years I cringe whenever I see that the vendor requires db_owner role permissions for the end users.  It's a question of security of course but also of stability for not just the database to which rights are provisioned but also for every other database on the instance.

1. Security

Any end user with db_owner permissions can grant rights as they see fit for any other login on the server or user in the database.  Does that sound like something you would entrust Frank in Human Resources to do?  He has a history of contacting the help desk when his mouse batteries run low.  This right should be provisioned to just those key application administrators or DBAs who have responsibility over such things.

2. Security Again

Does Janice, your new Marketing Supervisor need access to the payroll information for senior management?  Probably not.  If that is the case then why are you giving her access to query that information from your database along with every other column in every other table or view?  Congratulations Janice now has the ability to see how much she's making compared to everyone else in the company.  She also can delete data from the manufacturing tables or give herself a raise.  Or at least she can if she has db_owner role permissions.

3. Do You Want All Users to Be DBAs?

You've granted all users of this particular application db_owner role permissions as the vendor required.  Now your interns on the bottling line in plant 7 can drop any user table and change the schema of the products table.  They can add records to the products table so you're now officially manufacturing and selling such sought after items as "Dog Mascara" and "Prince Albert in a Can".  Do Claire and Shelly in Sales need to run DBCC CHECKDB?  As my Magic 8-Ball loves to say: "Chances are Slim."  Yet by granting db_owner permissions those are all things they are able to do.  How many users of this application do you have?  Welll whatever that count is you've just created that many Database Administrators.

4. It Affects EVERY Database on the Instance

I recently wrote an article here where I made the statement that granting db_owner permissons has a possible impact on every database - not just the database where rights are being provisioned.  I was met with a bit of shock and disagreement over that assertion.  What if we settle this with a bit of code though.  Code and data doesn't lie. 

My reasoning for the statement is simple enough: anyone with db_owner permissions can grow a database file in such a way to fill the volume it's hosted on.  That action then affects any database with storage on that volume as well.  This is the same for data or transaction log files as can be seen in the following example:

Session #1:  Create New Login and User with db_owner permissions

USE [master]GOCREATE LOGIN [foo] WITH PASSWORD=N'Some_really_difficult_pa$$word', DEFAULT_DATABASE=[iDBA], CHECK_EXPIRATION=OFF, CHECK_POLICY=ONGOUSE [iDBA]GOCREATE USER [foo] FOR LOGIN [foo]ALTER ROLE [db_owner] ADD MEMBER [foo]GO

Session #2: Log in as the new user inside of SQL Server Management Studio and alter the data and transaction log files

--===============--BEFORE--===============SELECT name, type_desc, (size * (8/1024)) AS size_mb, (growth * (8/1024)) AS growth_mb FROM iDBA.sys.database_files--===============--CAUSE SOME TROUBLE--===============ALTER DATABASE [iDBA] MODIFY FILE ( NAME = N'iDBA', SIZE = 3000MB , FILEGROWTH = 512MB )ALTER DATABASE [iDBA] MODIFY FILE ( NAME = N'iDBA_log', SIZE = 2000MB , FILEGROWTH = 512MB )GO--===============--AFTER--===============SELECT name, type_desc, (size * (8/1024)) AS size_mb, (growth * (8/1024)) AS growth_mb FROM iDBA.sys.database_files

Before:

After:

Case closed there.  That's why I always question the need to provision the db_owner database role permission on a shared / consolidated SQL Server.  That decision affects every user - not just users for that particular application.

5. Application Security Is Not All-Encompassing

The first argument I usually hear from a software vendor when I push back on allowing db_owner permissions grants tends to be flavors of blocking actions in the application:

"We only allow certain functions to be performed from the application."

"We lock that down in the application."

"Users can't access that information from the application."

Guess what ladies and gentlemen, in 5 minutes or less I can download Microsoft SQL Server Express Edition from microsoft.com, install it on my laptop and connect to the database if I know my login and password (using SQL authentication) or via my network login using Trusted Authentication and I'm now able to perform any action allowed through db_owner permissions that are "locked-down" via the application interface. 

The argument from the vendor is irrelevant when you can get to the database without using the application.

So what do I recommend?  Provisioning only the access needed to only the objects needed.  Create a custom database role around those permissions so you can save yourself time and effort when rolling out to new users and you are good to go in a secure and stable implementation.