Who should own the database?7
January 15, 2018 by Kenneth Fisher
TL;DR; SA, or if you are really paranoid then you can create a disabled SQL login with minimal permissions and use that.
The database has to be owned by someone, doesn’t it? (FYI, yes it does) So who? How about Bob in accounting. It’s the accounting database. That makes sense right? Well, maybe not. We don’t really want to give Bob that kind of power over the database. He’s not even a developer. How about Mary then? She’s the lead developer. Well, no disrespect to developers but I’m certainly not going to give a developer that level of power.
Side note: The owner of the database not only has the ability to make any changes they want to the structure of the tables, the code within SPs, etc but they also have the ability to take backups (possibly breaking the backup chain), set the database to single_user (keeping everyone else out of it) and drop the database (everyone knows why that’s bad right?). On top of all that they can’t be denied anything. (Yes, the actual database owner is one of the exceptions to the rule that DENY trumps everything.)
So back to owners. How about Amy? She’s one of the DBAs. That would be perfect! She created the database (or possibly restored it) so she is already the current owner anyway! Well, what happens when she leaves and her account is terminated? Honestly dear reader, not much. 99% of everything will still run correctly and you probably won’t notice for a while. You can get the odd strange error though so it’s probably best not. How about the AD group that all the DBAs belong to? Well, no, not allowed. Windows/AD groups cannot own a database.
All right, well how about sa? It can already do anything it wants so it’s not like we are granting additional permissions, it can’t quit or be fired, and no one should be using it anyway. Heck, you probably have sa disabled, right? You know what? That’s actually perfectly reasonable! In fact sa is a really common id to use as the database owner.
The only risk is if the database becomes TRUSTWORTHY. Then you can create stored procedures that can act as sa. It’s honestly not a huge risk even then since you have to have impersonate permissions to create a procedure with the EXECUTE AS clause. Meaning that in order to create that stored procedure you have been explicitly granted the ability to impersonate a member of the db_owner role (please, please, please don’t do this) or be a member of db_owner yourself. That does mean that someone with db_owner can become a sysadmin but again, fairly minor risk. TRUSTWORTHY isn’t that common, and hopefully, if you are using it you understand the risks and are avoiding dangerous permissions.
But, a risk is a risk. So if we are feeling particularly paranoid and/or have a very sensitive system the easiest solution is to create a SQL login (with a stupidly complex password since no one is ever going to log in as it), disable it, and then use that as the owner.
- The database owner can be located in two different places. sys.databases and the dbo user in the database. In theory these should always be the same, but I have seen cases where they are different. If so this can be corrected by changing the database owner.
- The database owner can be changed by using sp_changedbowner, but that has been deprecated, so in later versions of SQL use ALTER AUTHORIZATION.
- If a user already exists in the database they can not be made the database owner.
- If a login has been made a database owner then that login can not be removed until it is no longer being used as the database owner of a database.
Category: Impersonation, Microsoft SQL Server, Security, SQLServerPedia Syndication | Tags: best practices, database permissions, microsoft sql server, security
7 thoughts on “Who should own the database?”
Leave a Reply Cancel reply
This site uses Akismet to reduce spam. Learn how your comment data is processed.
I worked somewhere once where the database owner was some random SQL Login and when I changed the owner to sa it broke stuff. They were using the SQL Login to handle the cross-database querying permissions. Oops.
Oops indeed 🙂 Of course if they are using the database owner for user processes it deserved to get broken.
Thank you Kenneth you learn something new every day, I just assumed that the dbowner has to be an enabled login without testing it 🙂
Glad I could help!
Here’s a nice blog and poll and demo of the consequences: http://www.insidesql.org/blogs/andreaswolter/2014/06/sql-server-database-ownership-survey-results-recommendations
Very cool post. Thanks for sharing!
[…] Well, I could write out three or four paragraphs on that, or I can share my favorite article on What account should own the databases and why. A very thorough explanation covering all the bases. Highly […]