SQL Server Database Ownership: survey results & recommendations
You may remember the survey on database ownership which I launched several months ago.
In the following, I am now presenting the results and giving my official recommendation for a best practice for security in terms of database ownership. First, if you still need the script:
Server 1: http://j.mp/13_SQL_DBO_Survey
Now first the results. 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:
57% of all databases belong to sa itself. Actually, this is better than expected. But let’s dive deeper – what’s the server role behind the remaining 42%?
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.
If you are interested in the plain numbers, here they go:
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:
In the interest of time I will focus this post on recommendation rather than explaining all the risks involved. At the end though I will provide some links for further reading.
So what are the general variations of database ownership? Let me start with the most common and actually WORST possibilities (Yes, I mean it exactly as I say 😉 ):
- Some other SQL-Account with sysadmin privileges
- Windows Login with sysadmin privileges
A first improvement(? – really?):
4. Any of the above with Status = Disabled
5. A ”shared” account without any special server role or permissions (aka “1 Account per Server”)
6. 1 Account per Database
7. 1 Account per Application
8. 1 Account per Group of databases
+ all of them not only Disabled but with a Denied Connect-Permission
Depending on your environment: Any of 5, 6, 7 or 8:
Create a specific Login without any extra permissions + Deny Connect.
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.
Call for actions:
Check your databases. You can find my script here: Security-Check-Script & Survey: SQL Server Security – Database-Owners, critical Permissions and role membership Now when you start with securing your databases from database-ownership standpoint, you have to make sure that the very account does exist at any sever where this database gets restored/failed over. Usually you will have a technique in place already to synchronize your server-level principals to your other servers. So this is just one or several more of them. 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
I hope this was helpful.
If you have any questions feel free to comment. Let me finish up with some links for further readings:
Highly recommended reading:
More on Disabling and Deny Connect:
More on Trustworthy: