Determine and Alter Ownership of All Objects in a SQL Database

The following script can be used to determine which objects in a SQL database are owned by a schema other than the one which is specified in the script and creates, as its output, a list of commands to update the ownership to that aforementioned schema.

Please note:  Just because you're able to change the ownership of objects in a database, doesn't mean that you should, or that this won't have consequences elsewhere.  I've used this script to update objects which were created as part of an older installation/upgrade package where we used create objects by passing along the credentials of the user executing the package, rather than simply creating all objects as belonging to 'dbo'.

This is the basic text of the script:

/*************************************************

Author: Chicago Computer Consultants [Charles E. Brinkman]
Date: 1.05.11
Title: Determine and Alter Ownership of All Objects in a SQL Database
Purpose:     

1. To determine if any objects in a database are associated with schemas other than the one specified in the script.
2. To generate a script capable of updating any objects which do not belong to the desired schema.

**********************************************/
SELECT          
     'ALTER SCHEMA TARGET_SCHEMA
      TRANSFER [' + B.NAME + '].' + A.NAME
FROM
     SYS.OBJECTS A INNER
JOIN          
     SYS.SCHEMAS B
ON
     A .SCHEMA_ID = B.SCHEMA_ID
WHERE
     B .NAME <> 'TARGET_SCHEMA'
AND
     B.NAME <> 'sys'
ORDER BY         
     TYPE_DESC DESC

For example, to search for any objects which are not owned by the schema 'dbo' and to generate a script capable of transfering the ownership of those objects to 'dbo' you would modify the above script to the following by simply replacing both instances of the words "TARGET_SCHEMA" with the word 'dbo':

SELECT          

     'ALTER SCHEMA dbo
     TRANSFER [' + B.NAME + '].' + A.NAME
FROM
     SYS.OBJECTS A INNER
JOIN          
     SYS.SCHEMAS B
ON
     A .SCHEMA_ID = B.SCHEMA_ID
WHERE
     B .NAME <> 'dbo'
AND
     B.NAME <> 'sys'
ORDER BY
     TYPE_DESC DESC

The output of this script will be similar to the following, with one line appearing for each object which is not owned by the dbo schema:

ALTER SCHEMA dbo TRANSFER [test].MY_TABLE_1
ALTER SCHEMA dbo TRANSFER [test].MY_SP_1
 
Copying these results into a new query window and executing them will update the schema of these objects from 'test' to 'dbo'.


Alternately, if you don't care about knowing which objects' ownership will be updated, you can also use the suggestion in this link from MS, which creates a SP to do this for you, but doesn't provide you with the same detail by way of feedback.  Let me know if you have any questions or run into any problems with the script, it's been very helpful for me in the past.