Every Database has a DBO or database owner set. Sometimes the owner is invalid, while most of the time the DBO is perfectly normal. It is easy to ignore whether a valid DBO is set or not. And then something breaks and you have to validate it. This article addresses the easy fix to validate the DBO.
In a recent article, I explored an oddity around the presence of an invalid database owner (dbo). That article can be found here. Near the end of that article, I gave a homework assignment.
Much like my High School calculus teacher, I have decided to show you the big red easy button. Of course, that doesn’t ever come until after the hard way is learned.
Prior to proceeding, I do recommend that you do read the aforementioned article. In that article, I demonstrate one specific case of a mismatching or invalid dbo. I also describe the more common version of the problem. In this “fix” article, the fix should address both types of issues.
Bad Naughty DBO
Since I am in the giving mood (and since I already had the solution scripted), I opted to share the script I use at clients to fix some of these issues. Now, granted, some discretion may need to be applied. Not all environments are the same and there may be various different requirements from one environment to the next. Please be sure to review and evaluate if you really want to set the dbo for the databases to a valid principal vs an invalid principal.
Not only does this script evaluate for dbo issues, but I capture several other issues at the same time. The fixes associated to those other issues are not included in this article, but it does present you with some interesting things to consider.
The DBO Fix Script
Without much more ado, you can download the fix it script here.
I am not going to go into details on the entire script. If you wish to have something explained, please leave a comment. Suffice it to say that this script loops through the databases to find a slew of settings and log those values to a temp table. From that temp table, I drive a couple of cursors to resolve various dbo issues.
Put a bow on it
SQL Server will always have a database owner for each database. Sometimes, the database owner appears to be missing. When this happens it can be for one of a couple of reasons. The first reason being that the user is invalid and the owner is “orphaned” for lack of a better term. This happens when the owner is specified in the user database but not the master. This condition results in an error that prevents opening of properties pages.
The other reason can be that there is just a disjointed issue between the master database and the user databases. As was shown in this article, the owner was showing as the ‘sa’ user in master but the user database had no owner specified. This condition results in the properties pages being able to be opened without error.
Interested in learning more from my back to basics collection? Check these out!
Want to learn more about your indexes? Try this index maintenance article or this index size article.
This is the tenth article in the 2021 “12 Days of Christmas” series. For the full list of articles, please visit this page.
The post DBO – Your Easy Homework Cheat Sheet Is Here first appeared on SQL RNNR.