Andreas Wolter (www.andreas-wolter.com) has worked for over a decade as trainer (MCT), consultant and architect for SQL Server systems. He is one of less than a dozen experts worldwide, who have been certified as Microsoft Certified Master (MCM) on SQL Server 2008 and additionally Microsoft Certified Solutions Master Data Platform (MCSM) on SQL Server 2012.

Being an active member of the PASS he is also a speaker at various international conferences since several years.

With his Germany-based company, Sarpedon Quality Lab (www.SarpedonQualityLab.com), he provides development and optimization of database systems, with a focus on scalable, performing and secure deployments across Europe and worldwide. You can also follow him at Twitter at twitter.com/AndreasWolter.

I received data from 58 different servers and 905 databases altogether. That’s not bad, and sufficient for my purpose of giving you, my readers, the opportunity to find out how others configure their servers.

Many thanks to all those who submitted!

You may still share results but I can’t promise how soon I can include them. (Here is the survey plus the script for collection) So now to the details. I put the most interesting data in charts. The most obvious issue is that of the external owner’s account, which is most often and not very surprisingly sa:

Ok, that changes the picture quite a bit. Almost 80% of all Database owners are sysadmin. So that is by no means any better than sa.Then some other accounts follow, which means those have low privileges (“excellent”), and then comes dbcreator, securityadmin, that are later followed by some other high privileged server roles, though with much less power.

So in other words: only 7% of all those databases have been looked at with security in mind by only using low privileged accounts as owners.

I did include some of the security-wise critical database- & server configurations:

Is the database set to be “Trustworthy”?

Is the database set to have “Database chaining on”?

Is the Server set to have “cross database chaining on”?

Those are actually the even more important results.

Since the system databases need to have a different setting by default, I am excluding them, making it a total of 847 User databases.Of which 30 have the trustworthy bit set to on, and 35 have the database chaining. What you can’t see in this graph, but what I can tell from the raw data, is that those 30 “trustworthy” databases all are owned by a sysadmin. And THIS now is the biggest security-hole in this area!Here a graph on that:

The most simple approach and yet better than sa is: one database owner per server.Example for (5):

Database1 owned by DBOwner

Database2 owned by DBOwner

Database3 owned by DBOwner

Simple and self-explanatory.

The other extreme and most secure is: per database.Example for (6):

Database1 owned by DBOwner_Database1

Database2 owned by DBOwner_Database2

Database3 owned by DBOwner_Database3

Database4 owned by DBOwner_Database4

Some applications use a number of different databases. For them it’s perfectly fine to use the same database owner account. So create an account per application.

Example for (7):

App1Database1 owned by DBOwner_App1

App1Database2 owned by DBOwner_App1

App2Database1 owned by DBOwner_App2

App2Database owned by DBOwner_App2

Another approach is kind of a compromise between 1 Database-Owner Account per Server and One per database: Define the level of security needed per database. Then create a dedicated account for the most critical Databases. And for the others use a shared owner/account, possibly divided in 2 or more groups.

Example for (8):

CriticalDatabase1 owned by DBOwner_Level1Dedicated1

CriticalDatabase2 owned by DBOwner_ Level1Dedicated2

Level2Database1 owned by DBOwner_Level2

Level2Database2 owned by DBOwner_Level2

I hope my samples give you an idea. :-)

So why this effort? Let me put it this way: ”Why not sa?”. First: If you think about it, it actually makes little sense that the highest privileged account in SQL Server is being recommended by so many, even professionals + in Whitepapers (!) – when security is the focus. It is really wrong, as wrong as it could possibly get. I mean, as you can see, there are other options out there.The top reason why SA keeps getting recommended is administration itself: It eases the setup for failover and regular database restores, since SA is always available at any server and hence a broken database owner can be avoided with almost no extra work. But that’s “only” from a perspective of maintenance. With regard to security it is totally on contrary to the Principle of least privilege.

It may not matter a lot, if everything else is tightened, but that’s hardly a thing to rely on especially in bigger environments where things change and many people have access and permissions to. Especially in the context of the trustworthy-setting for a database, this completely opens the system for privilege escalation attacks from inside. It is then a piece of cake to gain system level permissions once you are for example in the db_owner database group – like many applications are, if they are not sysadmin already. - Remember: the owner of a database cannot be denied anything inside and with his database. So he can change structure, create backups, break log-backup-chain and also drop it completely.

And since the attack starts from inside, it really doesn’t matter whether the sa/sysadmin account is disabled as you may now realize.

Having a dedicated account with zero special permissions as database owner prevents database principals from gaining system level permissions as a sysadmin has, even in the case of the database being trustworthy. And trustworthy is one of the dirty little shortcuts for developers implementing CLR code inside the database and avoiding the hassle of having to use certificates under certain conditions. The same is often done for code that needs to get server-level data from inside the database.

Also make sure you fully understand your environment and possibly application needs before you just change the owner of your databases. You can start by reading through the links at the bottom.

Vote for an improvement in SQL Server:I have created a suggestion as Connect Item which tackles this problem. My idea is having Microsoft include a special “DBOwner” Account at server level by default, which not only pre-exists and has not permissions, but also never compares to another. I think this would make it much easier to get rid of the habit of “sa” everywhere by also making it simple to maintain. Please vote here: Providing a special Server principal for Database Ownership