changing object owner with a cursor

[rhofing]

Hello, I am running a job that uses a cursor and dynamic sql to change the object owner of all the tables in a database. The problem is that the owner is changed for most of the tables, but not all. This varies with each running of the script.

If I run the scipt in Query Analyzer it works perfectly every time. It only has the problem when it is part of a scheduled job. Any ideas?

Thanks!

Ric

 

[tlbroadbent]

It is difficult to debug code if we can't see it. Please post your code. Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

If you want to get the best answer for your question read faq183-874.

 

[rhofing]

Here is the code:

-- this script uses a cursor to extract the names of all the
-- user tables. It then builds an SQL string dynamically to change
-- the owner of each of the tables.
set nocount on
declare c1 cursor dynamic
for select [name]
from usd.dbo.sysobjects
where xtype='U'

declare @tablename varchar(100), @execstring VARCHAR(1000)

open c1
fetch c1 into @tablename
while (@@fetch_status = 0)
begin
select @execstring = 'exec sp_changeobjectowner ' + '''usd.' + @tablename + '''' + ',' + '''' + 'ahd' + ''''
exec (@execstring)
fetch c1 into @tablename
end -- while

close c1
deallocate c1


If I run this in a job it changes the name of most, but not all, of the tables. If I run it in Query Analyzer it works perfectly every time.

Ric

 

[tlbroadbent]

What is the name of the database? Is it usd? Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

If you want to get the best answer for your question read faq183-874.

 

[rhofing]

The name of the database is usd, and the owner of the tables is also usd. I want to change the owner to ahd (database name does not change). The ahd user does exist.

Ric

 

[tlbroadbent]

I failed to note the Dynamic Cursor declaration. Remove the dynamic or change it to read_only. Let me know if this helps.

Another thing you should do is to filter the cursor to only choose tables with an owner of USD. Otherwise, the code will try to change the owner of all tables, including those not owned by USD.

set nocount on
declare c1 cursor dynamic
for select o.[name]
from dbo.sysobjects o
Join dbo.sysusers u
on o.uid=u.uid
where o.xtype='U'
And u.name='usd' Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

If you want to get the best answer for your question read faq183-874.