asked on

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.

ASKER CERTIFIED SOLUTION

Aneesh

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Aneesh

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

ptjcb

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

SCS1ST

ASKER

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.

SOLUTION

ptjcb

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SCS1ST

ASKER

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?

SCS1ST

ASKER

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?

ptjcb

Ok...can you open dbo._qryStaff_Active? Is there an explicit reference to elliott._qryStaff_Active_LoanOfficer? Do the same for the other query.

SCS1ST

ASKER

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?

ptjcb

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_fast

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_fast

oops i am so late.... sorry ptjcb

Aneesh

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 ?

ptjcb

Should be:

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

SCS1ST

ASKER

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.

ptjcb

Yes - drop the function, change owner, create function

SCS1ST

ASKER

That worked. Thanks very much for all your help and patience while I learn this.