February 20, 2008 at 8:49 am
HI,
I have a database that has 5 db_owner. Two of them need to be removed and are not being replaced.
What are the proper steps to remove those two db_owner?
Do I need to transer their owned obect to one of the existing db_owner?
February 20, 2008 at 9:08 am
If the objecst are not owner by dbo, then you might need to. If they are owner by dbo, just remove those two users from the role.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
February 20, 2008 at 9:12 am
Some objects are owned by these db_owner that have left. How can I transfer these objects to the dbo?
February 20, 2008 at 9:17 am
The stored procedure sp_changeobjectowner can do that. However, is your server SQL Server 2000 or 2005? Changing the owner will break any object references for stored procedures and views which specify owner will no longer reference objects correctly. If you are on SQL Server 2005, you can change the owner of the schema to dbo and then delete the users. This will prevent the object reference breaks.
K. Brian Kelley
@kbriankelley
February 20, 2008 at 9:25 am
I am using SQL 2000. So what should I first?
Here is what I found.
Script out the existing persmissions. Run the command. Reapply the script and change the object-owner in the permissions script before running.
Is there anything that I forgot?
p.s.: You are a lifesaver!
February 20, 2008 at 9:30 am
You can do it that way, script and re-run, or you can just use sp_changeobjectowner for each object and see what breaks and then fix those up as you encounter them. Tables should for the most part be fine. It's your views, stored procedures, and functions you're most worried about. Also, using sp_changeobjectowner maintains the data in those tables. Scripting and re-running does not. If there is no data that has to be retained, it may be the better approach.
K. Brian Kelley
@kbriankelley
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply