• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 501
  • Last Modified:

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.
0
SCS1ST
Asked:
SCS1ST
  • 6
  • 6
  • 3
  • +1
2 Solutions
 
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

0
 
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
0
 
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
0
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 
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.  
0
 
ptjcbCommented:
Try to find the dependencies

EXEC sp_depends @objname = N'elliott._qryStaff_Active_LoanOfficer'
0
 
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?
0
 
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?
0
 
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.
0
 
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?
0
 
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.
0
 
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.

0
 
imran_fastCommented:
oops i am so late.... sorry ptjcb
0
 
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 ?
0
 
ptjcbCommented:
Should be:

ALTER FUNCTION dbo._qryStaff_Active()
RETURNS TABLE
AS
RETURN ( SELECT     dbo._qryStaff_Active_LoanOfficer.*
FROM         dbo._qryStaff_Active_LoanOfficer )
0
 
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.  
0
 
ptjcbCommented:
Yes - drop the function, change owner, create function
0
 
SCS1STAuthor Commented:
That worked.  Thanks very much for all your help and patience while I learn this.
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

  • 6
  • 6
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now