Select the product you need help with
- Internet Explorer
- Windows Phone
- More products
INF: SQL Server Procedure to Change Object Owner
Article ID: 275312 - View products that this article applies to.
This article was previously published under Q275312
Sometimes, you may need to change the owner of an object. This article contains a code sample that you can use to change ownership of objects.
If you have numerous objects that require an ownership change, you can use the following SQL Server stored procedure to ease the process:
Example of UsageTo change all the objects owned by user test to dbo, run the following code:
The code finds all the objects that are owned by test and generates a series of SQL statements by using the sp_changeobjectowner stored procedure. You can then examine and run the statements in the SQL Server Query Analyzer.
If a new user already has an object with the same name as the current user a warning displays along with the object name. For example, if both test and dbo own a table named world, then the following error message displays:
If you attempt to drop a user that still owns objects in the database, the process fails with the following error message:
WARNING *** dbo.world already exists ***
15183 : 'The user owns objects in the database and cannot be dropped'