September 28, 2010 at 3:32 am
Hello folks
Today i was moving system databases model and msdb from local hard drive to new SAN storage. After detach and attach these two db's, their owner become my windows account which also has sysadmin privlege. Now I try to update catalog view sysdatabases by running the following command (the sid of 0x01 is the owner for master database which is still sa):
UPDATE sysdatabases
SET sid = 0x01
WHERE name IN ('model', 'msdb')
And enounctered the following error message:
Ad hoc updates to system catalogs are not allowed.
Bazinga!
September 28, 2010 at 3:46 am
If I use:
use model
go
exec sp_changedbowner 'sa'
go
error message is:
Cannot change the owner of the master, model, tempdb or distribution database.
Bazinga!
September 28, 2010 at 4:40 am
I believe if you are logged in as 'sa' when you do the move the owner should be sa. Is that an option for you?
September 28, 2010 at 5:12 am
raistlinx (9/28/2010)
I believe if you are logged in as 'sa' when you do the move the owner should be sa. Is that an option for you?
Nope, running as 'sa' is not an option.
Bazinga!
September 28, 2010 at 8:47 am
Is this an option?
Detach those databases
Connect to SSMS as SA
Attach the databases using CREATE DATABASE...FOR ATTACH
Pradeep Adiga
Blog: sqldbadiaries.com
Twitter: @pradeepadiga
September 30, 2010 at 1:12 am
Adiga (9/28/2010)
Is this an option?Detach those databases
Connect to SSMS as SA
Attach the databases using CREATE DATABASE...FOR ATTACH
Thanks Adiga, your method actually works!
Bazinga!
September 6, 2012 at 11:32 am
how does the previous option work for a system database (model) since it already exists, how can you create it?
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply