Microsoft KB Archive/275312

= INF: SQL Server Procedure to Change Object Owner =

Article ID: 275312

Article Last Modified on 10/31/2003

-

APPLIES TO


 * Microsoft SQL Server 7.0 Standard Edition
 * Microsoft SQL Server 2000 Standard Edition

-



This article was previously published under Q275312



SUMMARY
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] GO

SET QUOTED_IDENTIFIER OFF    SET ANSI_NULLS  ON GO

CREATE proc chObjOwner( @usrName varchar(20), @newUsrName varchar(50)) as -- @usrName is the current user -- @newUsrName is the new user

set nocount on declare @uid int                  -- UID of the user declare @objName varchar(50)      -- Object name owned by user declare @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 static for select name from sysobjects where uid = @uid

open chObjOwnerCur if @@cursor_rows = 0 begin print 'Error: No objects owned by ' + @usrName close chObjOwnerCur deallocate chObjOwnerCur return 1 end

fetch next from chObjOwnerCur into @objName

while @@fetch_status = 0 begin set @currObjName = @newUsrName + &quot;.&quot; + @objName if (object_id(@currObjName) > 0) print 'WARNING *** ' + @currObjName + ' already exists ***' set @outStr = &quot;sp_changeobjectowner '&quot; + @usrName + &quot;.&quot; + @objName + &quot;','&quot; + @newUsrName + &quot;'&quot; print @outStr print 'go' fetch next from chObjOwnerCur into @objName end

close chObjOwnerCur deallocate chObjOwnerCur set nocount off return 0

GO SET 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 *** dbo.world already exists ***

sp_changeobjectowner 'test.world','dbo'

go

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'

Additional query words: alias group role security

Keywords: kbinfo kbcodesnippet KB275312

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.