Changing owner of model database to ''SA''

Question

Post reply

Changing owner of model database to ''SA''

Erich Brinker

SSCrazy

Points: 2973
More actions
October 18, 2005 at 8:48 am

#111012

I am stumped on this one...
We had someone who somehow managed to change the owner of the model database to her nt login.
I was curious if anyone knows a reasonably safe way to change the owner of the model database. I have seen ideas like modifying the sp_changedbowner to not check for model and things of that nature, but that doesn't seem to be the safe way to do this.

Any help would be appreciated..
Erich

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

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

gbn SSCertifiable Points: 6429 More actions · Answer 1

Try this:

back it up

reconnect to the box as "sa"

restore it

(not tried it myself)

andrewkane17 SSCertifiable Points: 7529 More actions · Answer 2

Look up sp_changedbowner in bol, should give you what you need

Martin Mojzisek SSCrazy Points: 2207 More actions · Answer 3

Just out of curiosity I reproduced the situation and tested Shawn's solution and unfortunatelly it didn't work for me. What seems to solve the problem was to stop sql server, start from command prompt without recovery of all dbs except master (sqlservr -m -c /T3608), detach model db and reattach as sa user. All this is documented by MS but I wouldn't call it a safe solution, the situation itself is very unsafe in my opinion.

Erich Brinker SSCrazy Points: 2973 More actions · Answer 4

Thank you for your help. I tried the restore as sa and that does not work for me either. The sp_changedbowner is also not valid for model database.

Thanks for the help Martin, that was the solution I had found. I was hoping for an easier less risky solution. I did have 1 question about turning off the Trace flag, do you just stop sql server then run the sqlservr -m -c to restart without the trace flag on?

Thanks again for the help.

-Erich

Martin Mojzisek SSCrazy Points: 2207 More actions · Answer 5

After you're done you should stop sql server from the cmd window (Ctrl+C) and start normally as a service. The trace flag will no longer be valid after this and normal startup recovery of all dbs occur.