November 8, 2016 at 6:29 pm
Hi,
How can we make database owner to sa default with the database refresh. With the database refresh, we are getting the person name that are refreshing
November 8, 2016 at 11:57 pm
ramana3327 (11/8/2016)
Hi,How can we make database owner to sa default with the database refresh. With the database refresh, we are getting the person name that are refreshing
You can use sp_changedbowner to change the owner to sa
😎
November 11, 2016 at 7:42 am
I want to make it as default not manually run the command.
November 11, 2016 at 7:47 am
As far as I know, you can't - RESTORE DATABASE will leave the database owned by the user who performed the restore. You'll need to add the change of owner as a part of your restore process. Use ALTER AUTHORIZATION: sp_changedbowner is deprecated.
John
November 11, 2016 at 8:00 am
Hi,
I noticed in one instance when I restored the database using different server backup, after the restore the database owner is sa. But when I created the new database the database owner is my login name. So there is some process which is changing database owner automatically with restore but I didn't see any SP or anything. How can we achieve this?
November 11, 2016 at 8:16 am
Like I said, the only way I know of doing it is to change the database owner separately. If you did it before and the owner was sa, could it be that you were logged in as sa? Come to think of it, you could try putting [font="Courier New"]EXECUTE AS USER = 'sa'[/font] before your RESTORE statement. Don't forget to [font="Courier New"]REVERT[/font] afterwards.
John
November 11, 2016 at 8:20 am
I am sure, I was not logged as sa.
November 11, 2016 at 8:23 am
ramana3327 (11/11/2016)
I am sure, I was not logged as sa.
Then your login is a member of the sysadmin role
😎
November 11, 2016 at 8:32 am
Yes. I am the member of sysadmin in all instances. But when I restored a database in other instance, after the restore it will display my login as database owner except in this one instance.
November 11, 2016 at 8:37 am
Eirikur Eiriksson (11/11/2016)
ramana3327 (11/11/2016)
I am sure, I was not logged as sa.Then your login is a member of the sysadmin role
😎
Interesting. I'm a sysadmin and I just restored a database, and the owner was my own login, not sa. Then I tried restoring a database that already existed and was owned by sa, and the owner afterwards was indeed sa. Mystery solved, probably.
John
November 11, 2016 at 10:58 am
I have to ask what's so difficult about using sp_changedbowner to change the owner. That's what the procedure is written to do.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply