Who Owns That Database?
Introduction
Recently I was testing a new backup product, SQL Litespeed, and while setting up a stored procedure to backup my databases I ran into an interesting issue. Whenever I ran this statement:
insert #MyTempTable exec sp_helpdb
I received an error about not being able to insert a null value into the table. This was slightly confusing as I'd declared the temptable to accept null values. So I decided to dig in a bit further and execute only the sp_helpdb section.
Surprisingly enough, this returned the same error, unable to insert a NULL value, column does not allow NULL values. Specifically the owner column.
Hmmmmmm.
After a moment of sitting there with a puzzling look on my face, I executed a simple:
select * from master.dbo.sysdatabases
to see where I might be having an issue. No NULLs in the sid column, which should map to the db owner. Now I was more annoyed than confused and needed to dig in further.
Fortunately Microsoft allows us to examine the code behind the system stored procedures. Actually maybe they couldn't figure out a way to protect that code either (since no one I know has either), so you can look at it. In Query Analyzer, I browsed to the master database and selected the sp_helpdb procedure from the Object Browser. A quick right click and a script as create gave me the text of the procedure.
Very quickly I zeroed in on this section of the code:
/* ** Initialize #spdbdesc from sysdatabases */insert into #spdbdesc (dbname, owner, created, dbid, cmptlevel) select name, suser_sname(sid), convert(nvarchar(11), crdate), dbid, cmptlevel from master.dbo.sysdatabases where (@dbname is null or name = @dbname)
Since this is the area where the temp table is being populated and there is a column called "owner", I was guessing this was the problem area.
And I was right.
If I ran just the SELECT portion of this insert, I found that the owner column indeed did return a NULL value.
For model and msdb!
That didn't seem right. Check master, that has "sa" as the owner. Check a couple other SQL 2000 servers and they have "sa" as the owners. There is a "SID" for this column, so what is the issue? The issue is the suser_sname() function, which returns the name from the domain controller for this SID. In my case, however, the domain account was deleted, so there is no matching SID. As a result, the function returns NULL.
OK, kind of interesting. How do I fix this? Well it turns out to be very simple. Though not as simple as I expected.
My first thought was to use sp_changedbowner to change the owner to "sa". This procedure is run from the database and takes the name of an account to change to. No big deal, give it a try. It runs and returns
Server: Msg 15110, Level 16, State 1, Procedure sp_changedbowner, Line 46 The proposed new database owner is already a user in the database.
Not exactly what I was hoping for. A quick dig through Books Online confirmed that this is expected and either there is no easy workaround, or I'm not very good at searching Books Online. I'll let you decide which is more likely.
I suppose I could have dropped sa, which is mapped to dbo for the databases, but that seemed risky to me and I wasn't really looking for a server outage to fix this little annoyance. Instead I decided to try a simple, albeit probably not always recommended technique.
I know that the SID for the "sa" account is always 0x01, and I know that I can run a simple command that will allow me to update the system tables. My first test was on model because, well it's not a critical database, and I know I can always grab model from another server and attach it here. I ran:
sp_configure 'allow updates', 1 reconfigure with override update sysdatabases set sid = 0x01 where name = 'model' sp_configure 'allow updates', 0 reconfigure with override
and it worked. A refresh in Enterprise Manager confirmed that the owner of "model" was now "sa". I repeated this for "msdb" and sp_helpdb, my procedure, and my backups started running!
Conclusions
Whew this was a fun day. Actually, the total time was only about an hour, but it is amazing how one little thing leads to another and you end up fixing some problems that you didn't know you had.
Hopefully I've entertained you for a few minutes at my expense and stuck a thought in your head that you'll remember when you find a database with no owner. In the days of detach and attach, I'm sure you'll encounter this at some point. I welcome your comments below and look forward to reading them.
Steve Jones
©dkRanch.net February 2003