Post reply

How to change the owner of a User-defined data TYpe in SQL 2000

  • October 8, 2008 at 10:52 am

    #72137

    Hi,

    I have 6 User-Defined Data Types in a database that I have restored and I need to change the owner.

    I found http://support.microsoft.com/kb/327123/en-us but this involves changing the data type for the columns that use the UDT's, dropping them and then changing the columns back to use that UDT once they have been recreated.

    Is there any way that I can just change the owner without dropping them?

    Thanks in advance.

    Richard

  • October 8, 2008 at 11:00 am

    #882456

    edited after poutting foot in mouth:

    i had thought just like you that the stored procedure sp_changeobjectowner should do what you want;

    but the article you mentioned states if it's in use you gotta re-create it...sorry.

    this won't work, just for other objects:

    sp_changeobjectowner [ @objname = ] 'object' , [ @newowner = ] 'owner'

    so wouldn't you just use

    exec changeobjectowner 'myUDFDatatype','dbo'

    or maybe

    exec changeobjectowner 'existing_owner.myUDFDatatype','dbo'

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • October 8, 2008 at 11:27 am

    #882472

    Yeah I changed all the objects that had the incorrect owner using sp_changeobjectowner but these were left. Tried your suggestions but to no avail.

    Looks like I will have to drop and recreate - not a simple procedure as when I tried to alter the data type for the columns I found out that there are other dependencies so have to deal with them first - could be a fun exercise!!!

    Thanks!

  • October 8, 2008 at 12:08 pm

    #882495

    You cannot alter a UDT if it is used by objects.

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply