2

In the course of reading up on SQL Server security best practices, I learned that databases should generally be owned by a dedicated, low-privileged login (sometimes a separate one for each DB or each application), per these articles.

I've also learned that you can't change the owner of master, model, and tempdb, but you can change the owner of msdb. My question is: should you?

The main concern with high-privileged database owners is that the TRUSTWORTHY setting could be enabled for that DB, and msdb has to have TRUSTWORTHY enabled. It seems like that's a good argument for changing the owner of msdb to a low-privileged account, but I don't know if that could cause problems with instance functionality, and I haven't been able to find any articles or whitepapers even discussing the topic. The closest I've seen is this one, which stops short of making any recommendations.

Can anyone provide any insight as to the possible effects of changing the owner of msdb?

Improve this question
2

Brent Ozar's sp_blitz check on this sums it up well, I think:

...there’s no short answer for best practice.

And he does reference Andreas' article as well, acknowledging that is an ideal state. But often times ideal states are not feasible or reasonable in practice.

I don't know that taking the best practice approach (in this case) is indeed reasonable or feasible when looking at many enterprise scale deployments or when managing any large number of instances. I'm often satisfied enough to just use sa personally.

A lot of the community's best practice checks default to looking for sa as a database owner in general, because having a commonly used one does indeed help from an administrative perspective. For example, the popular PowerShell module dbatools's functon Test-DbaDbOwner defaults to looking for sa (citing Dan Guzman as the best practice source to do so) as the owner of all databases (though you can certainly specify a custom one if desired) just like Brent's scripts do.

I think in this case whether or not to do it comes down to personal opinion/choice based on circumstance. If you can handle the additional burden, then by all means it is ideal to do so. But most of the time in my experience, I think the extra work, complexity, and potential for time consuming problems outweighs the narrow benefits.

Finally, when you ask:

Can anyone provide any insight as to the possible effects of changing the owner of msdb?

I'm not sure what you mean, but I think Andreas covers the topic fairly well to show what the benefits/effects of changing the owner are.

Further Reading:

Improve this answer
  • Thanks for the detailed response! I was already aware that many sources suggest simply using sa as the owner for all databases. Your comment about the trade-off between extra complexity vs narrow benefits is helpful in framing the decision. Mostly, though, I'm wondering if anyone has knowledge of what will happen if I change the ownership of the msdb system database specifically, not just a user database. Will there be ownership chaining issues with Agent jobs or backups? Permissions issues? – RollingDBA Mar 20 '19 at 22:17
  • You should test it out to see! I wouldn't expect there to be any issues, though, since the database owners aren't really used much by way of actual security. – LowlyDBA - John McCall Mar 21 '19 at 14:31
  • One example of why not to use sa is that you therefore can use the Agent. If the agent has anywhere near admin rights (often yes), then one can do most anything including decrypt Linked Server Logins. :) – clifton_h Mar 22 '19 at 5:31
  • @clifton_h, can you clarify what you mean here? Giving the Agent admin privileges allows users to escalate their privileges? Is this only exploitable when msdb is owned by a sysadmin? If not, what other measures can be taken against it? – RollingDBA Mar 22 '19 at 15:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.