A common task I run into is changing the ownership on a database table or stored procedure in SQL Server. There are times when the owner is set incorrectly or differently what what I would like. This is especially true upgrading from SQL 2000 to SQL 2005
For example, if a table is created using 'CREATE TABLE Products' but I was logged in as 'scott' to the 'orcsweb' database, the owner would be 'scott' and the fully qualified object name would be 'orcsweb.scott.Products'. This isn't always best. In most cases I prefer 'dbo' as the owner since it makes queries easier when using mulitple users.
Of course if you created the table like so: 'CREATE TABLE dbomultiple.Products' then the owner will be 'dbo' but in the case where this was created wrong in the first place how do you change this afterward?
The manual way if you only have a couple tables or objects to change is to use sp_changeobjectowner. This is great and what I use many times.
But, what about the times when you have quite a few to change and doing it manually isn't reasonable or desirable? David Penton, Microsoft MVP - SQL Server, has put together a great SQL script that I think everyone should keep handy. (Yes, I did get his permission to post this)
DECLARE @old sysname, @new sysname, @sql varchar(1000)
SELECT
@old = 'oldOwner_CHANGE_THIS'
, @new = 'dbo'
, @sql = '
IF EXISTS (SELECT NULL FROM INFORMATION_SCHEMA.TABLES
WHERE
QUOTENAME(TABLE_SCHEMA)+''.''+QUOTENAME(TABLE_NAME) = ''?''
AND TABLE_SCHEMA = ''' + @old + '''
)
EXECUTE sp_changeobjectowner ''?'', ''' + @new + ''''
EXECUTE sp_MSforeachtable @sql
-------------------------------
The same can be done to stored procedures. This example works differently though. It doesn't actually make the change. Save the results in text and then paste the result back into Query Analyzer and run it.
-------------------------------
DECLARE @oldOwner sysname, @newOwner sysname
SELECT
@oldOwner = 'oldOwner_CHANGE_THIS'
, @newOwner = 'dbo'
select 'EXECUTE sp_changeobjectowner '''+QUOTENAME(a.SPECIFIC_SCHEMA)+'.'+QUOTENAME(a.ROUTINE_NAME)+''','''+@newOwner+''''
from
INFORMATION_SCHEMA.ROUTINES a
where
a.ROUTINE_TYPE = 'PROCEDURE'
AND a.SPECIFIC_SCHEMA = @oldOwner
AND
OBJECTPROPERTY(OBJECT_ID(QUOTENAME(a.SPECIFIC_SCHEMA)+'.'+QUOTENAME(a.ROUTINE_NAME)), 'IsMSShipped') = 0