msiedle
asked on
Mass Change Database Owner
Hi all
I need to change all tables in my database that are owned by user1 to user2, and I know how to do this table by table, but is there a way to make this a little more automated so that it automatically iterates through all my tables and sets the owner to 'user2'? ...or am I stuck writing the following for each table?
sp_changeobjectowner
'user1.eHR_Accred_Communic
Cheers,
Mark
I need to change all tables in my database that are owned by user1 to user2, and I know how to do this table by table, but is there a way to make this a little more automated so that it automatically iterates through all my tables and sets the owner to 'user2'? ...or am I stuck writing the following for each table?
sp_changeobjectowner
'user1.eHR_Accred_Communic
Cheers,
Mark
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
had to mod the user's around, but other than that perfect ...
DECLARE @dbObject varchar(200)
DECLARE CurDbObjects CURSOR FOR
SELECT su.NAME + '.' + so.NAME AS OBJECT
FROM sysobjects so , sysusers su
WHERE so.UID = su.UID AND su.NAME = 'user1'
AND su.NAME NOT LIKE 'INFORMATION%' AND XTYPE= 'U'ORDER BY so.NAME
OPEN CurDbObjects
FETCH NEXT FROM CurDbObjects INTO @dbObject
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC ('sp_changeobjectowner ''' + @dbObject + ''' , ''user2''')
FETCH NEXT FROM CurDbObjects INTO @dbObject
END
CLOSE CurDbObjects
DEALLOCATE CurDbObjects
thanks :-)