DB Owner ''unknown'' on Model and MSDB databases

Question

DB Owner ''unknown'' on Model and MSDB databases

Jpotucek

SSCoach

Points: 18583
More actions
March 16, 2005 at 7:00 am

#88291

I have 2 System Databases - Model and MSDB - that list in EM as Owner 'UNKNOWN'
I think the owner may have been an account that has been deleted. WHen I attempt to sp_changedbowner 'sa' I get this error:
Server: Msg 15109, Level 16, State 1, Procedure sp_changedbowner, Line 22
Cannot change the owner of the master database.
What are the ramifications of leaving it like this and does anyone know how I can re-assign the owner? thank you!

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

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

Dexter Plaras Ten Centuries Points: 1000 More actions · Answer 1

Follow the procedures from this link: http://support.microsoft.com/default.aspx?scid=kb;en-us;224071

Make sure you login as SA. The ramnifications of leaving it like this is you'll still be able to execute jobs but new jobs created won't execute on the specified schedule date/time.

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

A little code snippetto make the change (I'd probably restart SQL afterwards as well).

---

--- fix_db_sid.sql

---

use master

go

exec sp_configure 'allow updates',1

go

reconfigure with override

go

update sysdatabases

set sysdatabases.sid = s.sid

from syslogins as s

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

and s.loginname = 'sa'

go

checkpoint

go

exec sp_configure 'allow updates',0

go

reconfigure with override

go

---

--- end of fix_db_sid.sql

---

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

noeld SSC Guru Points: 96590 More actions · Answer 3

I would not run that code !

sp_changedbowner not only touches the sid from sysdatabases it also updates sysusers (sid, status and updatedate)

Try the detach/attach procedures better

* Noel

Jpotucek SSCoach Points: 18583 More actions · Answer 4

How about this code????

Use Model

go

exec sp_configure 'allow', 1

reconfigure with override

go

declare @sid varbinary(85)

select @sid = sid from master.dbo.syslogins

where loginname = 'sa'

update master.dbo.sysdatabases

set sid = @sid

where dbid = db_id()

exec sp_configure 'allow', 0

reconfigure with override

I successfully ran this against my MDSE Instance and was able to change the 'Owner' on the Model and MSDB databases to 'something other than sa' and then back again. Any thoughts????????????????

noeld SSC Guru Points: 96590 More actions · Answer 5

you could do that even with the previous one too! But What I am trying to get at is that yours and previously posted code are only changing sysdatabases table and may be leaving inconsistencies in sysuser.

If you want to run any code just copy what sp_changedbowner does and comment out the check for the system databases

hth

* Noel

Jpotucek SSCoach Points: 18583 More actions · Answer 6

Thank you Noel. Looks like I need to look at the detach/attach procedures like you said. Thanks again