INF: SQL Server Procedure to Change Object Owner

Article translations Article translations
Article ID: 275312 - View products that this article applies to.
This article was previously published under Q275312
Expand all | Collapse all

On This Page

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 + "." + @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 @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'

Properties

Article ID: 275312 - Last Review: October 31, 2003 - Revision: 3.2
APPLIES TO
  • Microsoft SQL Server 7.0 Standard Edition
  • Microsoft SQL Server 2000 Standard Edition
Keywords: 
kbcodesnippet kbinfo KB275312

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com