change ownership of objects in sql server

In SAP, the system SID, database name, database username, schema name must be same, this makes life little difficult when you are moving the database from one enviorment to another as many times you have to change owner of objects from existing user to new user.

You might also have to change the object owners if you are accessing objects using ObjectOwner period (.) ObjectName in your TSQL statements. The following script will change owner ship of Tables, Stored Procedures, Functions etc.

This script is written and tested on  SQL Server 2000 only, technically it should work on SQL Server 2005 make sure you read http://msdn2.microsoft.com/en-us/library/ms177519.aspx

SET NOCOUNT ON

Declare @Old_Username sysname

Declare @New_Username sysname

Declare @Object_Name sysname

Declare @Adhoc_SQL varchar(200)

SET @Old_Username = 'dbo'

SET @New_Username = 'sqldba'

DECLARE User_Cursor CURSOR FOR

SELECT [name] FROM sysobjects WHERE OBJECTPROPERTY(id,'IsMSShipped') = 0

AND type IN ('U','P','FN','IF','V''X','TF')

AND USER_NAME(OBJECTPROPERTY(id,'OwnerID')) = @Old_Username

OPEN User_Cursor

FETCH NEXT FROM

User_Cursor

INTO

@Object_Name

WHILE @@FETCH_STATUS = 0

BEGIN

SET @Adhoc_SQL = 'EXEC sp_changeobjectowner '+

'@objname = ''' + @Old_Username + '.' + @Object_Name + ''', ' +

'@newowner = ''' + @New_Username + ''''

print @Adhoc_SQL

EXEC (@Adhoc_SQL)

print 'Object : ' + @object_name + ' Owner Changed to : ' + @New_Username

FETCH NEXT FROM

User_Cursor

INTO

@Object_Name

END

CLOSE User_Cursor

DEALLOCATE User_Cursor

If you want to change the ownership of objects owned by any user then, remove following line from the script.

AND USER_NAME(OBJECTPROPERTY(id,'OwnerID')) = @Old_Username

You can also change the object types to add or restrict the script to certain object types at following line

 AND type IN ('U','P','FN','IF','V''X','TF')

Add Comments Read Comments (0) 04/29/2008


SQL Server DBA

I am a SQL Server DBA with almost 9 years of experience in database technologies.

Right now I am in process of redesigning this website in ASP.NET 2.0 and AJAX. The main purpose of new SQL DBA website is to offer more help to database professionals and make it easy for me to update contents on regular basis.