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.
Why do you automatically execute the commands to change the database owner to ‘sa’ rather than just generate the commands so they can be applied if desired, and why do you set the user to user ‘sa’ rather than follow the MS recommendation to create a discrete user to be the database owner?
I knew what I was looking to do. However, you are welcome to just generate the commands. Many MS recommendations are utter garbage. They get many things wrong. A discrete user comes with plenty of other problems. If you wish to change it to another user, go ahead! Nothing in this article says it is the absolute ONLY way to do things. Just make sure that other user is not a domain user, will never expire, has all of the necessary permissions including permissions in other databases, will never be removed, etc. – before changing the user. Changing to sa not only ensures the change will not break, it is recommended by many community thought leaders (some still recommend a different discrete user and that is their choice).