September 5, 2008 at 4:19 am
As the title says. I can do this easily with a cursor, but I'm curious how to call a procedure (sp_changeobjectowner ) multiple time while looping through sysobjects. I wish to change ownership tables and SP's(xtype U and P), where UID has a specific value to 1, which is DBO.
I've been fiddling with a UDF, but I just can't get the option of calling sp_changeobjectowner for every hit I get on this statement:
SELECT t1.name, t2.name from sysobjects t1
inner join sysusers t2 on t1.uid = t2.uid
where t1.uid = 27
and (xtype = 'U'
or xtype = 'P')
Greetz,
Hans Brouwer
September 5, 2008 at 7:11 am
For the tables, and yes this is basically a wrapper around a cursor, you can use the undocumented sp_msforeachtable procedure. Something like this:
exec sp_msforeachtable @command1 = 'exec sp_changeobjectowner ''?"", ''dbo''' @whereand =' and O.uid = 27'
Or you can just generate a scipt like this and then copy and paste the results into QA:
Select
'Exec sp_changeobjectowner ' + name + '''dbo'''
From
sysobjects
Where
uid = 27 And
xtype in ('U', 'P')
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
September 5, 2008 at 8:01 am
Tnx for answering, Jack. Your second solution does work, though it's not what I had in mind.
I had not thought about using the hidden foreach.. procedures. I don;t think the solution you have given does work, however. The SP sp_changeobjectowner does not allow for a WHERE condition, and that is what you have developed, I think.
But it's worth looking into this; you've pointed me in the right direction.
Tnx again
Greetz,
Hans Brouwer
September 5, 2008 at 8:08 am
Forget my last remark: I did not really see how the @whereand is used. Yes, this is more to what I had in mind.
Tnx!
Greetz,
Hans Brouwer
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply