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.
More information
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 Usage

To change all the objects owned by user test to dbo, run the following code:
exec chObjOwner 'test','dbo'				
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:
WARNING *** already exists ***
sp_changeobjectowner '','dbo'
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