Who should own the database?

6

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.

Additional notes:

  • 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.

    6 thoughts on “Who should own the database?

    1. notarian says:

      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.

    2. Eugene van den Bergh says:

      Thank you Kenneth you learn something new every day, I just assumed that the dbowner has to be an enabled login without testing it 🙂

    Leave a Reply

    Fill in your details below or click an icon to log in:

    WordPress.com Logo

    You are commenting using your WordPress.com account. Log Out /  Change )

    Google+ photo

    You are commenting using your Google+ account. Log Out /  Change )

    Twitter picture

    You are commenting using your Twitter account. Log Out /  Change )

    Facebook photo

    You are commenting using your Facebook account. Log Out /  Change )

    Connecting to %s

    This site uses Akismet to reduce spam. Learn how your comment data is processed.

    Enter your email address to follow this blog and receive notifications of new posts by email.

    Join 2,465 other followers

    Follow me on Twitter

    ToadWorld Pro of the Month November 2013
    %d bloggers like this: