Sql-server – the best practice for the msdb database owner

msdbownersql server

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?

Best Answer

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:

Related Question