Changing the owner of tables

Taken from: http://weblogs.asp.net/owscott/archive/2004/01/30/65229.aspx

The below helps changing ownership of tables (not database. You can follow the same principle to change other types of objects). Please note running the below would could limit access to these tables.

DECLARE @old sysname, @new sysname, @sql varchar(1000)
SELECT
@old = 'OldOwner'
, @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

Changing database owner when dbo is associated with a sql or windows account

You may have run into a situation where the dbo role is associated with a sql or a windows account (as you create objects under that identity). The problem? If you attempt to delete the account or remove its mappings (User Mapping under the properties of that account), you will get the following error:

Drop failed for User 'dbo'.

...

Cannot drop the database owner. (Microsoft SQL Server, Error: 15181)

The best way that I have found to go around this is to re-associate dbo with "sa" by using the sp_changedbowner stored procedure:

EXEC sp_changedbowner 'sa'

(Note: make sure you use "USE databaseName" or run the SP in the context of the database)