Having a Database Owner is not something that most people think about until something breaks. Usually, people will just kind of ignore it because it is just so innocuous and uncommon for the owner to not be “present”. In this article, I will share an oddity that happens when the database owner is seemingly missing.
Usually, when a database owner is not properly configured, an error message will be thrown. I have run into an odd case where there is no error message and thus no obvious indicator that there is a problem.
In this oddball case, the owner showed as sa just about everywhere but in the GUI. Not only was it showing in the GUI, but many scripts also reported that the database owner was set. Despite all of this evidence, there was one spot in the GUI that disagreed with everything. Let’s dive in and check things out!
Owners in the GUI
Let’s start with the the problem screen in the GUI. Typically, when an owner is missing or invalid, the properties screens won’t open. In this case, the properties windows do open but you end up with odd results.
Now that we can see that the owner is a bit jacked up somewhere, we can look at a quick query.
As you can see from both of those screen shots, I have the same problem in those areas. If I look at other spots though, I get different results.
A Different Owner
Having consistency in your applications is pretty important. When I run into something that shows different results, I find it a tad annoying. In the following images, you will see that inconsistency compared to the previous images.
And in the following script results, I get the same inconsistency.
So, why are these results different than the first screenshots? Some of that boils down to the differences in how the database owner is determined. As it happens, the first script result shows what the difference between these results just happens to be. When you look closer at that script, you will see that it uses the following method to get the owner.
select suser_sname((select sid from sys.database_principals where name = N'dbo'));
How did I find that this was what SSMS was doing to get the owner? Well, it took me a bit but I eventually wised up and decided to use the power tools available to me. Which power tool is it that I decided to employ, you ask? Well, the number one tool to provide insight into your database server on the market that is free. That best power tool on the market happens to be Extended Events.
Capture the Owner
Through the use of Extended Events, I was able to throw a simple session together and troubleshoot the problem. In order to find the cause of the owner not displaying meant creating an XEvent session, running the session, opening the appropriate properties windows, and then reviewing session data. First, let’s look at how simple that XEvent session could be.
-- Create the Event Session IF EXISTS ( SELECT* FROMsys.server_event_sessions WHEREname = 'DBOwnerCheck' ) DROP EVENT SESSION DBOwnerCheck ON SERVER; GO EXECUTE xp_create_subdir 'C:DatabaseXE'; --not really needed due to use of just the ring_buffer GO CREATE EVENT SESSION [DBOwnerCheck] ON SERVER ADD EVENT sqlserver.sql_batch_completed (ACTION ( package0.event_sequence , sqlserver.client_app_name , sqlserver.client_hostname , sqlserver.client_pid , sqlserver.database_id , sqlserver.database_name , sqlserver.is_system , sqlserver.nt_username , sqlserver.request_id , sqlserver.server_instance_name , sqlserver.server_principal_name , sqlserver.session_id , sqlserver.sql_text , sqlserver.username ) WHERE ([sqlserver].[equal_i_sql_unicode_string]([sqlserver].[server_principal_name], N'mydomainjesuispuissant')) ) , ADD EVENT sqlserver.sql_batch_starting (ACTION ( package0.event_sequence , sqlserver.client_app_name , sqlserver.client_hostname , sqlserver.client_pid , sqlserver.database_id , sqlserver.database_name , sqlserver.is_system , sqlserver.nt_username , sqlserver.request_id , sqlserver.server_instance_name , sqlserver.server_principal_name , sqlserver.session_id ) WHERE ([sqlserver].[server_principal_name] = N'mydomainjesuispuissant') ) ADD TARGET package0.ring_buffer --just keep it in memory. Should be for a quick troubleshoot foray WITH ( MAX_MEMORY = 4096KB , EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS , MAX_DISPATCH_LATENCY = 30 SECONDS , MAX_EVENT_SIZE = 0KB , MEMORY_PARTITION_MODE = NONE , TRACK_CAUSALITY = ON , STARTUP_STATE = OFF ); GO ALTER EVENT SESSION DBOwnerCheck ON SERVER STATE = START; GO
Pow – Find that Owner
After starting the session shared in the preceding script, it is time to do the hard part. The hard part being that you have to demonstrate some patience while working through the iterative process.
As mentioned earlier, the process is not difficult. The process involves opening the properties pages and then reviewing the trace data. A rather boring, low skill task that is somewhat iterative and could be time consuming depending on the activity of the server. I am going to just skip right to the spot where I found the right query that SSMS runs when finding the database owner. That nugget of information is shown in the next screenshot.
Now that you can see what the root cause of this weird behavior, the next step is pretty simple in my opinion. The next step is simply to go create a script that loops through the databases and gathers some data to compare the database owner stored in the master database vs. what is stored in each user database. I will leave that as your homework assignment. 
I almost forgot, how does one fix this problem? The answer is pretty simple – use the “ALTER AUTHORIZATION” syntax. In the following script, I demonstrate how to use that syntax.
ALTER AUTHORIZATION ON DATABASE::[SomeDatabase] TO [sa];
See how easy that is?
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 deep technical information? Check these out!
Want to learn more about your indexes? Try this index maintenance article or this index size article.
This is the eighth article in the 2021 “12 Days of Christmas” series. For the full list of articles, please visit this page.
The post Owner is Missing in the GUI – An Easy Fix first appeared on SQL RNNR.