INF: SQL Server Procedure to Change Object Owner
This article has been archived. It is offered "as is" and will no longer be updated.
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:
if exists (select * from sysobjects where id = object_id(N'[dbo].[chObjOwner]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)drop procedure [dbo].[chObjOwner]GOSET QUOTED_IDENTIFIER OFF SET ANSI_NULLS ON GOCREATE proc chObjOwner( @usrName varchar(20), @newUsrName varchar(50))as-- @usrName is the current user-- @newUsrName is the new userset nocount ondeclare @uid int -- UID of the userdeclare @objName varchar(50) -- Object name owned by userdeclare @currObjName varchar(50) -- Checks for existing object owned by new user declare @outStr varchar(256) -- SQL command with 'sp_changeobjectowner'set @uid = user_id(@usrName)declare chObjOwnerCur cursor staticforselect name from sysobjects where uid = @uidopen chObjOwnerCurif @@cursor_rows = 0begin print 'Error: No objects owned by ' + @usrName close chObjOwnerCur deallocate chObjOwnerCur return 1endfetch next from chObjOwnerCur into @objNamewhile @@fetch_status = 0begin set @currObjName = @newUsrName + "." + @objName if (object_id(@currObjName) > 0) print 'WARNING *** ' + @currObjName + ' already exists ***' set @outStr = "sp_changeobjectowner '" + @usrName + "." + @objName + "','" + @newUsrName + "'" print @outStr print 'go' fetch next from chObjOwnerCur into @objNameendclose chObjOwnerCurdeallocate chObjOwnerCurset nocount offreturn 0GOSET QUOTED_IDENTIFIER OFF SET ANSI_NULLS ON GO
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.
exec chObjOwner 'test','dbo'
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:
WARNING *** dbo.world already exists ***If you attempt to drop a user that still owns objects in the database, the process fails with the following error message:
15183 : 'The user owns objects in the database and cannot be dropped'
alias group role security
Article ID: 275312 - Last Review: 12/05/2015 22:01:47 - Revision: 4.0
Microsoft SQL Server 7.0 Standard Edition, Microsoft SQL Server 2000 Standard Edition
- kbnosurvey kbarchive kbsqlsetup kbcodesnippet kbinfo KB275312