June 29, 2011 at 9:01 am
I've got a server where the model db owner is NULL. Actually, querying sys.databases shows a long SID, which I believe belongs to an Active Directory account which has since been deleted. I'd like to change the owner back to sa. (The SIDs for master, tempdb, and msdb are all 0x01.)
Using sp_changedbower gives an error saying I can't change the owner of master, model, or tempdb. Tried issuing an update against sys.databases and got an error saying no ad hoc updates of system tables are allowed. I tried detaching and re-attaching the db, as this post seemed to say that worked, but I got an error saying I couldn't detach system databases.
My next thought is to try to restore model db from another server where the owner is set correctly.
Does anyone else have any suggestions? Or see any dangers in restoring model from another server?
June 29, 2011 at 3:57 pm
Much lower risk is to backup the database, then log on with the sa account, then restore it from the backup you just created. The database will be owned by the account used to restore the database regardless of who owned it when it was backed up.
Cheers
Leo
Leo
Nothing in life is ever so complicated that with a little work it can't be made more complicated.
June 29, 2011 at 4:07 pm
I'd go with the backup/restore.
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
June 30, 2011 at 8:42 am
Thanks Leo.. I'll try that!
June 30, 2011 at 8:51 am
It did not work 🙁
June 30, 2011 at 9:01 am
You should be able to put the server in Maintenance Mode and make adjustments that way.
To do so, from the Services Window, stop the sql server and then add -m to the startup parameter line.
You have to make sure that you are the first to connect to the SQL Server because only one connection will be allowed.
Use sqlcmd and not Management Studio.
The sp_changedbowner command should work for model then.
June 30, 2011 at 9:18 am
michael.french 172 (6/30/2011)
You should be able to put the server in Maintenance Mode and make adjustments that way.To do so, from the Services Window, stop the sql server and then add -m to the startup parameter line.
You have to make sure that you are the first to connect to the SQL Server because only one connection will be allowed.
Use sqlcmd and not Management Studio.
The sp_changedbowner command should work for model then.
Thanks Michael. I had come across the same suggestion from a google search (but that post also said I needed to use the DAC too). The system in question is a production system, so this method is sort of a last resort for me. Truthfully, it's not causing me enough trouble to do that just yet. Just thought there might be a less intrusive way of doing it.
FYI, I also tried taking a backup of model from another server (same version) that was owned by sa and restoring it to my server. That didn't work either.
June 30, 2011 at 9:20 am
Just curious, have you checked for any corruption by chance?
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
June 30, 2011 at 9:30 am
Yeah, DBCC CHECKDB comes back clean for all system databases.
June 30, 2011 at 9:38 am
figured it might - but just to be sure.
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
June 30, 2011 at 9:48 am
Try this reference
http://msdn.microsoft.com/en-us/library/ms187359(v=SQL.90).aspx
The ownership of that database should not have changed - and it can't be changed.
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
July 5, 2011 at 3:41 am
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.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
July 5, 2011 at 5:17 am
So simple! :w00t::hehe:
July 5, 2011 at 9:28 am
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:
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
July 5, 2011 at 9:35 am
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]
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply