SP_HELP DB gives error.. Model and MSDB ''unknown'' owner

Question

SP_HELP DB gives error.. Model and MSDB ''unknown'' owner

Jpotucek

SSCoach

Points: 18583
More actions
November 15, 2005 at 11:07 am

#110375

When I run sp_helpdb against the master (or any other DB for that matter) I get the following error:
Server: Msg 515, Level 16, State 2, Procedure sp_helpdb, Line 53
Cannot insert the value NULL into column 'owner', table 'tempdb.dbo.#spdbdesc_ 0010001A6EF'; column does not allow nulls. INSERT fails. The statement has been terminated.
I have about 20 DBs on a SQL Server 2000 Standard Edition Instance with sp3a .
There have been no changes made to the Server or the SQL Server Instance in quite some time. But I did do some poking around and noticed that when I look at the properties of my Model and msdb databases in Enterprise Manager, they BOTH have {unknown} for Owner !!?? that CAN'T be a good thing !!
I checked BOL for sp_changedbowner and it says that you can't change the owner on master, msdb and ,model - so I'm a little leary to try it... Anyone have an suggestions? ... please..
When I do select sid, suser_sname(sid) from sysdatabases where name in ('model', msdb') I get this:
0x010500000000000515000000A27123298679482BA9AF192C51050000 NULL
0x010500000000000515000000A27123298679482BA9AF192C51050000 NULL
I just can't figure it out. This Instance was built over 2 years ago Before my time here) and I have documentation that I did several months ago that shows 'sa' as being owner of msdb and model.
Can I safely run run sp_changedbowner 'sa' on the model and msbd databases? And if not, does anyone know how to fix this?????

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Yelena Varshal SSC-Dedicated Points: 34286 More actions · Answer 1

Hi,

"Unknown owner" happens when the owner can not be resolved: for example someone's Windows login was the owner and this Windows login does not exsit anymore or Domain controller is not available to resolve the name.

Sa should be the owner of system databases with SID 0x01. Someone probably reattached or recreated these databases using his Windows login for connection (???).

sp_changedbowner contains the following code:

-- CANT CHANGE OWNER OF MASTER/MODEL/TEMPDB --

if db_name() in ('master', 'model', 'tempdb')

begin

raiserror(15109,-1,-1)

return(1)

end

I would read code for sp_changedbowner. It mostly does checking then updates sysdatabases. You may run code similar to sp_changedbowner but without restriction for your problem databases (on your own risk, test on the test server first. I did not test it) and change the owner to sa

Regards,Yelena Varsha

Jpotucek SSCoach Points: 18583 More actions · Answer 2

I suppose it's possible that someone's NT login was the owner and it was deleted. I have always been able to run sp_helpdb with no problems and I have some old documentation that i did (a couple of months ago) that shows 'sa' as owner of model and msdb. Does anyone know the ramifications of leaving them like this? I don't really have an instance to test on right now and this is production.. ???

Martin Mojzisek SSCrazy Points: 2207 More actions · Answer 3

For the original problem see this thread

http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=5&messageid=116255

Unfortunately this deals with user databases.

I don't know any specific ramifications, but I'm sure this is not something I wanted to have on my production server. To get rid of it - either follow Yelena's suggestion (no experience myself) or you can do this if you can afford some downtime.

1.Start SQL Server in a single mode (sqlservr -m -c /T3608)

2.Connect as sa and issue: exec sp_detach_db 'model'

3.issue sp_attach_db 'model',

'C:\Program Files\Microsoft SQL Server\MSSQL\Data\model.mdf',

'C:\Program Files\Microsoft SQL Server\MSSQL\Data\modellog.ldf'

4.Stop SQL Server and start as a service

do the same for msdb (in this order). Don't forget to check or alter the paths. I tested this procedure in test env and it worked fine but test for yourself in any case before you do anything on live server.

Jpotucek SSCoach Points: 18583 More actions · Answer 4

Thank you for the excellent suggestion. if the act of detaching and reattaching Model and MSDB will actually reassign the SID and show 'sa' as the owner again, then I'll want to try it...

What I'm having a hard time figuring out is how to recreate my problem on another Instance so I can see if detaching and reattaching actually fixes it?????

Rudyx - the Doctor SSC-Forever Points: 43695 More actions · Answer 5

To recreate the problem you'll probably have to edit the system tables on another server ... if you can do that, then why not just fix the system tables on the original problem server ?

We actually had this issue on a server recently, a vendor had installed SQL and 'customized' it (screwed it up royally). We booted them out and when we removed the domain credentials, viola, we had the same error.

Well, in any even, here's a script to fix things:

exec sp_configure 'allow updates',1

reconfigure with override

go

begin tran

update sysdatabases set sid = 0x01

where name in ('msdb', 'model')

select *

from sysdatabases

commit tran

go

exec sp_configure 'allow updates',0

reconfigure with override

go

RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."

Jpotucek SSCoach Points: 18583 More actions · Answer 6

Rudy,

Thanks much. I tested here on a SQL Server Personal Edition that I have running - Changed Model and msdb to an NT id SID and then back to 'sa' and it worked fine.

I'm going to try the same test on a similar Server setup (not Production) once i get it ready .. but it looks like this will do the trick!!

Thanks again