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
Server 2: gallery.technet.microsoft.com/scriptcenter/Database-Owners-role-3af181f5/
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.
Possibilities
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 😉 ):
- SA-Account
- Some other SQL-Account with sysadmin privileges
- Windows Login with sysadmin privileges
A first improvement(? – really?):
4. Any of the above with Status = Disabled
And then:
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
My Recommendation:
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:
DISABLE and DENY LOGIN, DENY USER & Effect on Impersonation and Permissions
More on Trustworthy:
The TRUSTWORHY bit database property in SQL Server 2005
Extending Database Impersonation by Using EXECUTE AS
Discussions:
Database/Object Ownership Misalignment
database ownership – sa disabled
Happy Securing
Andreas
Pleeease i need a copy of that script, i can´t find it anywhere and all links are not working
Thanks for reminding me that this script is not reachable at the old location any more.
I need to find a new place and will update this then.
Feel free to remind me again if it’s not done in like 2 weeks.
Hi Andreas
Was the Connect item you link to closed by Microsoft before they migrated the issues over to UserVoice? I can’t seem to find anything at UserVoice in order to vote it up. Not sure if it’s worth recreating?
Hi Simon,
as I am now part of that very Security Team of Microsoft, I do not need to recreate this.
The whole subject of SoD is on my desk, and this certainly touches it.
On the other hand it is certainly helpful if people like you from the community keep reminding us (and my superiors) of what you feel is important.
I’ll do my best to tackle this problem, but I cannot commit to any timeframe.
Andreas
Fantastic! Apologies, I didn’t realise you’d replied (actually forgot that I’d left the comment on this article and not the earlier one). I was just reminded of it when I was looking at restored databases where the “owner” is different depending on whether you look at the dbo row’s sid in sys.database_principals or the owner_sid in sys.databases (the former is used to display the owner name in the “Files” page of the SSMS DB properties window, the latter is used in the “General” page).
That said, I don’t feel that it’s a massively important thing to have a separate devoted owner principal; documentation explaining the principles behind the…principals is probably enough!
Andreas,
I have a question about this. I have a bunch of databases that are “owned” by a specific user (domain\username). That user no longer works there. I have a few questions about having a disabled ‘sa’ (or any disabled sysadmin account) be the owner of a database.
1. The fact that the ‘sa’ account is disabled…will that effect proper functionality of the database? i.e. backup/restore, normal read/write activity etc.?
2. If not, can this change (from domain user to disabled-sa) be made ONLINE? or will it have to be during a maintenance window?
3. Can you talk a little about what EXACTLY this “owner” is there for (especially when the owner can be a disabled sysadmin account)? It’s a little confusing for me.
Thank you my friend!
MD
Hello Mohammad,
thank you for your patience and reposting this question here.
This helps me to remind all potential readers, that DISABLED accounts only protect from logging in from outside.
Bu the account is still very much “alive” and can be impersonated just like any other account.
I wrote about this here: http://andreas-wolter.com/en/disable-and-deny-login-deny-user-effect-on-impersonation-and-permissions/
So what really matters is that that account has no elevated permissions himself.
But I will still answer 1 and 2:
1) No, a disabled owner has no effect on any such activities. I cannot think of anything else than “not being able to use for Logging in”
2) yes, you can change the owner at any time. It only has effect when it is actually being used for authentication (which it should not be as this is what a hacker would abuse)
which leads to 3:
in a nutshell: SQL Server has this concept of “ownership” for any securable. An owner is a principal who has full control over the respective entity. You cannot deny anything to an owner (database owner, schema owner etc.)
And then you have the concept of ownership-chaining. This is by default not enabled between databases (“cross-database”) but the Trustworthy but can make use of the owner as well, as I explain in my latest article (http://andreas-wolter.com/en/1810_privilege-escalation-to-sysadmin-via-trustworthy-database/)
And this is why it is important that the owner is a LOW-privileged user. not so much whether enabled or not, as we are not talking about interactive login-scenarios for attacks from inside
I hope this helps
I do realize this subject is quite confusing (and not explained in a simple way). Maybe it need another blog..
.
Hi Andreas, thank you for a brilliant article. Can I ask, in your opinion, can you see an issue with the following scenario? We have created a domain account which is a member of Domain Users. It has Password Never Expires, User Cannot Change Password and Account is Disabled. I have created a new database with my SysAdmin account (which made me the owner) then changed the database owner with ALTER AUTHORIZATION ON DATABASE::[database_name] TO [new_Domain_account]; Now the owner of my new database is the Domain account that is actually disabled and when I check Logins on the SQL Instance it doesn’t exist there..! In fact, it never existed on the instance. I have tried this on SQL Server 2008 and 2012. Am I missing something as this seems to be secure but I can’t believe that I haven’t found any sort of reference to this approach on the Internet..! Thanks, Michael
Hello Michael,
Thank you for your patience. It took me a while to make time for a repro. Now I did, and I can confirm that:
It is perfectly possible to use ANY Windows Account (disabled or not) that is NOT a Login in SQL Server and use it as a Database Owner (Via ALTER AUTHORIZATION as well as the old sp_changedbowner). Can be either a Local account or a Domain Account.
The GUI will complain because it does some extra checking.
This is clearly against documentation. And the fact that it works pre 2012 implies that it is not to support SQL Azure Database (where this might make sense).
So, in my eyes, it must be a bug.
I have asked the Microsoft SQL Server security team and will let you know when I have news.
Andreas