Use the sp_rename stored procedure to rename the table. Use the
ownership information when doing the rename (i.e., sp_rename 'foo.bar',
'dbo.baz').
If for some reason that won't work, you can do it the long way:
Edit your table creation scripts to explicitly include the ownership
information (i.e., change the table name "foo" to "dbo.foo"). Then, logon
with an account that has dbo privileges and then rerun the table creation
scripts. That will create new tables with the appropriate owner. Then,
run a query to insert all of the data in the old table into the new table,
and drop the old table.
Remember that the only user for which an object will default to
"dbo" ownership is the sa account. All other users will create objects
under their own ownership, even if they have the dbo privilege. It's
always a good idea to specify the owner in your queries, both when
creating and using objects. In fact, I have a personal rule that I will
specify the owner unless I have a reason not to do so. So far, I've never
needed to use an object with a different owner.
_____________________
Eric
_____________________
"You can tell the ideals of a nation by its advertisements."
-- Norman Douglas
"Jeff Small" <[EMAIL PROTECTED]>
08/23/2004 09:49 AM
Please respond to sql
To: SQL <[EMAIL PROTECTED]>
cc:
Subject: How to change ownership? (SQL 2K)
We have a database that has two tables with improper ownership. I want to
change the ownership back to "dbo". Where do I need to look to do this? I
have a DTS package that won't run because of the ownership of these two
tables....argh...
[Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
