opus:interactive | If These Servers Could Talk

This script will change the ownership of user objects in the target database to the specified user.

This situation often occurs when importing a database from another SQL server where a user account existed that does not, and will not exist on the new SQL server.

Run this script to update user object ownership before clearing the old/incorrect user from the database.

This script could be turned into a stored procedure as well and accept the new username as a passed parameter.

There are multiple ways to do this some of which are probably more glamorous – but hey, it works.

——————————————————————————

/*
This script will change the ownership of user objects in the target database to the specified user.
This situation often occurs when importing a database from another SQL server where a user account existed that does not, and will not exist on the new SQL server.
Run this script to update user object ownership before clearing the old/incorrect user from the database.
*/

SET NOCOUNT ON

/* Set the new owner name here in place of the ‘dbo’ user */
DECLARE @New_Owner_Name Sysname
SET @New_Owner_Name = ‘dbo’

DECLARE @Object_ID Int, @Object_Name Sysname, @Owner_ID Int, @Owner_Name Sysname, @Owner_Object_Name Sysname
DECLARE CUR_ReplaceObjectOwner CURSOR FOR

/* Run this select query by itself to see the set of user objects you are working with */
SELECT so.id AS “Object ID”, so.name AS “Object Name”, so.uid AS “Owner ID”, su.name AS “Owner Name”, ‘[' + su.name + '].’ + ‘[' + so.name + ']‘ AS “Owner + Object Name”
FROM sysobjects so, sysusers su
WHERE so.uid = su.uid
AND so.xtype IN (’U',’P',’FN’,'V’)
AND so.name NOT LIKE ‘%dt_%’
AND so.name NOT IN (’syssegments’, ’sysconstraints’)
ORDER BY xtype

/* Get the results from the query and load the first row into the cursor */
OPEN CUR_ReplaceObjectOwner
FETCH NEXT FROM CUR_ReplaceObjectOwner
INTO @Object_ID, @Object_Name, @Owner_ID, @Owner_Name, @Owner_Object_Name

WHILE @@FETCH_STATUS = 0
BEGIN
IF @New_Owner_Name != @Owner_Name
/* Make the object owner change here */
EXEC sp_changeobjectowner @Owner_Object_Name, @New_Owner_Name

/* Load the next row into the cursor */
FETCH NEXT FROM CUR_ReplaceObjectOwner
INTO @Object_ID, @Object_Name, @Owner_ID, @Owner_Name, @Owner_Object_Name
END

/* Clean up */
CLOSE CUR_ReplaceObjectOwner
DEALLOCATE CUR_ReplaceObjectOwner
SET NOCOUNT OFF

GO

——————————————————————————