November 7, 2006 at 12:18 pm
I need to create a job that after I restore a db from dev to test, I then need to change the table owners. The problem is that there are over 2000 tables in this database and three different owners. I have to change the owner of all tables who have owner 'Devuser' to 'Testuser', change owner 'Devapp' to owner 'Testapp' and owner 'dev' to owner 'test'.
I could just create 2000 statements like this, but I would need to create over 2000 of these statements.
sp_changeobjectowner 'Devuser.Fuyer' ,Testuser
GO
sp_changeobjectowner 'Devapp.Fupio' ,Testapp
GO
sp_changeobjectowner 'Dev.Ferw' ,Test
Does anyone know of a script I can run to automatically change the owners?
Thanks!
Thanks!
November 7, 2006 at 12:35 pm
Here is a stored procedure which may help:
CREATE PROCEDURE ChangeAllObjOwner (
@oldowner sysname,
@newowner sysname
)
AS
DECLARE @objname sysname
SET NOCOUNT ON
--check that the @oldowner exists in the database
IF USER_ID(@oldowner) IS NULL
BEGIN
RAISERROR ('The @oldowner passed does not exist in the database',
16, 1)
RETURN
END
--check that the @newowner exists in the database
IF USER_ID(@newowner) IS NULL
BEGIN
RAISERROR ('The @newowner passed does not exist in the database',
16, 1)
RETURN
END
DECLARE owner_cursor CURSOR FOR
SELECT name FROM sysobjects WHERE uid = USER_ID(@oldowner)
OPEN owner_cursor
FETCH NEXT FROM owner_cursor INTO @objname
WHILE (@@fetch_status <> -1)
BEGIN
SET @objname = @oldowner + '.' + @objname
EXEC sp_changeobjectowner @objname, @newowner
FETCH NEXT FROM owner_cursor INTO @objname
END
CLOSE owner_cursor
DEALLOCATE owner_cursor
GO
November 7, 2006 at 1:00 pm
Thanks a lot. I just tried it and it works great!
Thanks!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply