SQL Server Performance Forum – Threads Archive
Changing owner on MODELMy model db on one of my servers has the owner changed to my personal logon. Generally all databases at my site are owned by sa. I can’t figure out how to change it back to sa. exec sp_changedbowner ‘sa’ doesn’t work. I tried connecting as ‘sa’ and restoring the database, but this didn’t work either. Any ideas?
did you refreshed this database to see if sp_changedowner worked?
Thanks NEWDB but when I said sp_changedbowner didn’t work I meant I got an error : Server: Msg 15109, Level 16, State 1, Procedure sp_changedbowner, Line 22
Cannot change the owner of the master database. Looking this up in BOL I see ‘The owner of the master, model, or tempdb system databases cannot be changed.’ I am assuming this means that sp_changedbowner cannot be used to change the owner of a system db. I am wondering if someone knows of another way to do this.
Thanks Allen but as I’ve stated sp_changedbowner doesn’t work with system databases
BOL under sp_changedbowner in Remarks section, says: The owner of the master, model, or tempdb system databases cannot be changed. CanadaDBA
I could just UPDATE sysdatabases diectly so that the sid is the same as another database whose owner is correctly identified as ‘sa’ as in: sp_configure ‘allow updates’, 1
RECONFIGURE WITH OVERRIDE
set sid = (select sid from master.dbo.sysdatabases where name = ‘master’)
where name = ‘model’
sp_configure ‘allow updates’, 0
RECONFIGURE Anyone see a problem?
THis is not a good practice & unsupported to update the system tables directly, instead after you’ve created the database add one line to change that database owner to sa or desired login. By default sa is owner of system databases. Satya SKJ
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.
can you detach it and reattach it giving it sa as the owner? Cheers
Detach database is not available for system databases.
quote:Originally posted by Twan can you detach it and reattach it giving it sa as the owner? Cheers
Well, you can detach model database by using flag -T3608 in the startup parameters and then using QA. This option doesn’t come in EM though. For more information, please check: http://support.microsoft.com/default.aspx?scid=kb;en-us;224071 I don’t think it is a good practice to change db owner to anyone other than sa for system databases. Any other thoughts guys?
I updated the sysdatabases table as I mentioned and everything seems fine now. I agree that all system databases should be owned by ‘sa’. In a production environment all objects should be owned by ‘sa’ unless there is a compelling reason not too. I have a 3rd party product installed that requires a different owner for its databases. I not clear why but.. Thanks for all the input.
It’s not bad idea to let us know how you did it? Did you use the detached idea? Or what?
quote:Originally posted by fhanlon I updated the sysdatabases table as I mentioned and everything seems fine now…