How to change model db owner?

Question

How to change model db owner?

Viewing 5 posts - 16 through 19 (of 19 total)

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

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 1

EdVassie (7/5/2011)
Sometimes things are not working the way they should and you have to go behind the scenes to fix it. For example fixing satellites[/url]

Thanks for the chuckle on a what-would-be-a-Monday morning 😀

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

SQLRNNR SSC Guru Points: 281334 More actions · Answer 2

opc.three (7/5/2011)
EdVassie (7/5/2011)
However, if you do end up with model or another system db owned by something other than sa and want to set it back to its rightful owner, the way I have used in the past is to copy sp_change_dbowner to a new name and remove the code that prevents changing the ownership of a system db. You can then run your new SP to change ownership of the system databases.
No offense, but this approach just seems wrong to me. I am surprised that code to prevent an ownership change is simlpe T-SQL code, and is not buried in a dll somewhere...but maybe it's not as big a deal as I think to work around it. I guess the flashback I just had to the 2000 days when folks would hack the system tables freaked me out :sick:

Simple fix - And I hadn't even thought of it. Several system procs are similar in nature and a similar fix can be employed. Best be careful in these types of cases.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 3

🙂

SQLRNNR (7/5/2011)
opc.three (7/5/2011)
EdVassie (7/5/2011)
However, if you do end up with model or another system db owned by something other than sa and want to set it back to its rightful owner, the way I have used in the past is to copy sp_change_dbowner to a new name and remove the code that prevents changing the ownership of a system db. You can then run your new SP to change ownership of the system databases.
No offense, but this approach just seems wrong to me. I am surprised that code to prevent an ownership change is simlpe T-SQL code, and is not buried in a dll somewhere...but maybe it's not as big a deal as I think to work around it. I guess the flashback I just had to the 2000 days when folks would hack the system tables freaked me out :sick:
Simple fix - And I hadn't even thought of it. Several system procs are similar in nature and a similar fix can be employed. Best be careful in these types of cases.

I wouldn't worry too much about this one... but then again I backup all system dbs too :-).

SQLRNNR SSC Guru Points: 281334 More actions · Answer 4

Ninja's_RGR'us (7/5/2011)
🙂
SQLRNNR (7/5/2011)
opc.three (7/5/2011)
EdVassie (7/5/2011)
However, if you do end up with model or another system db owned by something other than sa and want to set it back to its rightful owner, the way I have used in the past is to copy sp_change_dbowner to a new name and remove the code that prevents changing the ownership of a system db. You can then run your new SP to change ownership of the system databases.
No offense, but this approach just seems wrong to me. I am surprised that code to prevent an ownership change is simlpe T-SQL code, and is not buried in a dll somewhere...but maybe it's not as big a deal as I think to work around it. I guess the flashback I just had to the 2000 days when folks would hack the system tables freaked me out :sick:
Simple fix - And I hadn't even thought of it. Several system procs are similar in nature and a similar fix can be employed. Best be careful in these types of cases.
I wouldn't worry too much about this one... but then again I backup all system dbs too :-).

Kind of my point - backup backup backup.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

shaun.stuart SSCertifiable Points: 7083 More actions · Answer 5

EdVassie (7/5/2011)
There are various ways the the ownership of system databases can be changed away from sa.
The most common is if you move a system database and use the detach/attach method, the owner gets set to the account doing the attach. BTW, my recommendation is do not move master, model or resource DBs from their default location - there is no integrity, performance or security gain to be had by this and you just add risk to your installation.
However, if you do end up with model or another system db owned by something other than sa and want to set it back to its rightful owner, the way I have used in the past is to copy sp_change_dbowner to a new name and remove the code that prevents changing the ownership of a system db. You can then run your new SP to change ownership of the system databases.

The trick of changing the system stored procedure works in 2000, but in 2005+, sp_changedbowner has been rewritten and this is no longer possible.

I may try the attach / detach trick though. But I have a feeling I will just get an error message saying I can't detach a system database..

Shaun