sp_changeobjectowner - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

sp_changeobjectowner

Author

Topic

victord
Yak Posting Veteran

64 Posts

Posted - 2003-04-16 : 05:27:35

Hello Folks,

I know we can use

EXEC sp_changeobjectowner 'authors', 'Corporate\GeorgeW'

to change a single object in a database.. But how do i change the owner of tables over 500 in a database.

Thanks in Advance,
Victord

macka
Posting Yak Master

162 Posts

Posted - 2003-04-16 : 05:51:41

victord,

DECLARE @name AS VARCHAR(255)
DECLARE curDB CURSOR FOR
--modify this query to suit your needs
SELECT name
FROM sysobjects
WHERE xtype = 'U'

OPEN curDB

FETCH NEXT FROM curDB into @name
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC sp_changeobjectowner @name, 'Corporate\GeorgeW'
FETCH NEXT FROM curDB into @name
END

CLOSE curDB
DEALLOCATE curDB

Cheers,

macka.

--
There are only 10 types of people in the world - Those who understand binary, and those who don't.

nr
SQLTeam MVY

12543 Posts

Posted - 2003-04-16 : 06:31:33

or (no reason not to use an explicit cursor for this but ...)

declare @s varchar(128)
select @s = ''
while @s < (select max(name) from sysobjects where xtype = 'U')
begin
select @s = min(name) from sysobjects where xtype = 'U' and name > @name
EXEC sp_changeobjectowner @name, 'Corporate\GeorgeW'
end

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

victord
Yak Posting Veteran

64 Posts

Posted - 2003-04-16 : 07:04:03

Macka,

Thanks for your reply.But the problem i am having now is that after changing the owner of the tables/objects it drops all existing permissions for the objects. How do i reapply the permissions.
Cheers,
victord

nr
SQLTeam MVY

12543 Posts

Posted - 2003-04-16 : 07:35:07

If they are all the same then add a grant statement to the loop

select @sql = 'grant all to usrrole on ' + @name
exec (@sql)

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

victord
Yak Posting Veteran

64 Posts

Posted - 2003-04-16 : 08:49:31

Thanks Nr,
I tried the first procedure you gave me but came up with erro
as follows :

Server: Msg 137, Level 15, State 2, Line 5
Must declare the variable '@name'.
Server: Msg 137, Level 15, State 1, Line 6
Must declare the variable '@name'.

There seems to be something missing in the procedure.

Macka,

Tried your procedure as well but came up with errors as follows:

Server: Msg 15001, Level 16, State 1, Procedure sp_changeobjectowner, Line 38
Object 'EMSLS' does not exist or is not a valid object for this operation.
Server: Msg 15001, Level 16, State 1, Procedure sp_changeobjectowner, Line 38
Object 'EMSPD' does not exist or is not a valid object for this operation.

This looks like a permission error to me.
Please help.

nr
SQLTeam MVY

12543 Posts

Posted - 2003-04-16 : 09:48:19

declare @name varchar(128)
select @name = ''
while @name < (select max(name) from sysobjects where xtype = 'U')
begin
select @name = min(name) from sysobjects where xtype = 'U' and name > @name
EXEC sp_changeobjectowner @name , 'Corporate\GeorgeW'
select @sql = 'grant all to usrrole on Corporate\GeorgeW.' + @name
exec (@sql)
end

Suspect the tables are not all owned by the user you are running this under.
Include uid in the select to get objects owned by your user or include the current owner in the change statement.
Mighet be better to use the information_schema views.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources