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')