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')
Advertisements
MySQL HOSTING
MySQL hosting provider, clustering and replication supported. DBA Services Included.
SQL SERVER HOSTING
Reliable, powerful SQL Server 2008 hosting with ASP.NET on Windows 2008 Servers.
SQL Server DBA
I am a
SQL Server DBA with almost 9 years of experience in database
technologies.
I am again in process of redesigning this website using ASP.NET 2.0 and AJAX. The main purpose
of new SQL DBA website is to offer more features and make it easy for
me to update contents on regular basis.