Still trying to change owner of objects

I have been working of changing owners of all the objects in a SQL db for two days now.  Thanks to some very knowledgeable people on EE I have gotten scripts to do them all at once rather than one at a time.  I have managed to change owners of the Tables, Views and Stored Procedures.  However I can't get the functions to switch owners.  Here's the script that I'm using:

DECLARE @dbObject VARCHAR(255)
DECLARE CurDbObjects CURsoR FOR
SELECT su.NAME + '.' + so.NAME AS OBJECT
FROM sysobjects so , sysusers su
WHERE so.UID = su.UID AND su.NAME <> 'dbo'
AND su.NAME NOT LIKE 'INFORMATION%' AND XTYPE = 'FN'ORDER BY so.NAME


OPEN CurDbObjects
FETCH NEXT FROM CurDbObjects INTO @dbObject
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC ('sp_changeobjectowner ''' + @dbObject + ''' , ''dbo''')
FETCH NEXT FROM CurDbObjects INTO @dbObject
END
CLOSE CurDbObjects
DEALLOCATE CurDbObjects

Can anyone tell me why this isn't working and yet did on the other objects.  Thanks.
SCS1STAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Aneesh RetnakaranDatabase AdministratorCommented:
SCS1ST,
> AND su.NAME NOT LIKE 'INFORMATION%' AND XTYPE = 'FN'ORDER BY so.NAME
replace the above line with this

AND su.NAME NOT LIKE 'INFORMATION%' AND XTYPE IN ('FN','P','U','V')ORDER BY so.NAME

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Aneesh RetnakaranDatabase AdministratorCommented:
there are 3 types of Type values for functions

FN = Scalar function
IF = Inlined table-function
TF = Table function

AND su.NAME NOT LIKE 'INFORMATION%' AND XTYPE IN ('FN','IF','TF')ORDER BY so.NAME


for the functions
ptjcbCommented:
Replace with this
 AND su.NAME NOT LIKE 'INFORMATION%' AND XTYPE IN ( 'TF', 'FN') ORDER BY so.NAME

You must have a table function in your sysobjects list
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

SCS1STAuthor Commented:
I get this error when I use that script:  (And none of the functions changed owner)  

Server: Msg 15505, Level 16, State 1, Procedure sp_changeobjectowner, Line 63
Cannot change owner of object 'elliott._qryStaff_Active_LoanOfficer' or one of its child objects because the new owner 'dbo' already has an object with the same name.

This the one table that I have not been able to change owner because there is a Function with that exact name and it wouldn't let me rename the function so that I could change the owner of the table to dbo.  
ptjcbCommented:
Try to find the dependencies

EXEC sp_depends @objname = N'elliott._qryStaff_Active_LoanOfficer'
SCS1STAuthor Commented:
aneeshattingal - The last script you gave worked on the functions.  Thanks.  Now the only object I have left is that one table that has the same name as the function with a dbo owner.  I can't rename the function, so how do I chance the owner of the table?
SCS1STAuthor Commented:
This is what I get when using EXEC sp_depends @objname = N'elliott._qryStaff_Active_LoanOfficer':  

dbo._qryStaff_Active                    inline function      
dbo._qrystaff_Active_loanofficer    inline function      

Now what do I do?
ptjcbCommented:
Ok...can you open dbo._qryStaff_Active? Is there an explicit reference to elliott._qryStaff_Active_LoanOfficer? Do the same for the other query.
SCS1STAuthor Commented:
CREATE FUNCTION dbo._qrystaff_Active_loanofficer()
RETURNS TABLE
AS
RETURN ( SELECT     elliott._qryStaff_Active_LoanOfficer.*
FROM         elliott._qryStaff_Active_LoanOfficer )

AND

CREATE FUNCTION dbo._qryStaff_Active()
RETURNS TABLE
AS
RETURN ( SELECT     elliott._qryStaff_Active_LoanOfficer.*
FROM         elliott._qryStaff_Active_LoanOfficer )

I think I'm starting to understand this a little.  Both queries reference the table owned by elliot.  So couldn't I just delete the two queries, then change the owner of the _qryStaff_Active_LoanOfficer table to dbo, then recreate the queries?  Is it that simple?
ptjcbCommented:
That - or just do this:

ALTER FUNCTION dbo._qrystaff_Active_loanofficer()
RETURNS TABLE
AS
RETURN ( SELECT     dbo._qryStaff_Active_LoanOfficer.*
FROM         dbo._qryStaff_Active_LoanOfficer )

CREATE FUNCTION dbo._qryStaff_Active()
RETURNS TABLE
AS
RETURN ( SELECT     dbo._qryStaff_Active_LoanOfficer.*
FROM         dbo._qryStaff_Active_LoanOfficer )

Run these two queries that alter the function to look for dbo instead of elliott. Now you should be able to change the table.
imran_fastCommented:

use this to change the owner of the table
sp_changeobjectowner tablename, newownername

you cannot rename a function through query analyzer the only way is to drop and recreate the function.

if you don't want to drop  and recreate from query analyzer  go to Enterprise manager and rename it there  it will automatically drop and recreate the function.

imran_fastCommented:
oops i am so late.... sorry ptjcb
Aneesh RetnakaranDatabase AdministratorCommented:
imran,
>you cannot rename a function through query analyzer the only way is to drop and recreate the function.

i am not pretty sure, but can't we use sp_rename to rename the function ?
ptjcbCommented:
Should be:

ALTER FUNCTION dbo._qryStaff_Active()
RETURNS TABLE
AS
RETURN ( SELECT     dbo._qryStaff_Active_LoanOfficer.*
FROM         dbo._qryStaff_Active_LoanOfficer )
SCS1STAuthor Commented:
ptjcb - That query won't work becasue there isn't a dbo._qryStaff_Active_LoanOfficer.  Only an Elliott.._qryStaff_Active_LoanOfficer and when I try to change owners of that table it won't let me because is says there is already an object named that owned by dbo.  Which is the inline function.  So I'm thinking my only option is to delete the function.  Change owners of the table then recreate the function with a different name.  
ptjcbCommented:
Yes - drop the function, change owner, create function
SCS1STAuthor Commented:
That worked.  Thanks very much for all your help and patience while I learn this.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.