Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2004

    Unanswered: Change ownership for User Defined Data Types


    Does anybody know an easy way to change User Defined Data Types ownership to “dbo”, without dropping dependent objects?

    I know that there is a st. proc sp_changeobject owner, but it does not deal with this particular db objects. Is there a similar st.procedure or script that would do the same operation?

    Thank you

  2. #2
    Join Date
    Jun 2003
    Provided Answers: 1
    There are no dependent object to user-defined datatypes. If you try scripting out a table that was created with user-defined datatypes, you'll find it scripts the original datatype.
    Neither do objects "inherit" changes made to User-defined datatype.
    Thus: User-defined datatypes are candidates for the most useless and misleading features of SQL Server, perhaps exceeded only by the Maintenance Plan Wizard.
    If it's not practically useful, then it's practically useless.

    blindman "sqlblindman"

  3. #3
    Join Date
    Feb 2004
    Well, may be I expressed myself wrong. I was trying to change ownership for UDDTs, and I the only way I knew was to drop them and recreate as "dbo". But these UDDTs were already used by many tables and stored procedures. So I would need to drop those as well and then restore the data. Too much hassle.
    Anyway, I have already found the solution. This is the script:

    sp_configure 'Allow Update',1
    Reconfigure WITH OVERRIDE

    Update sysTypes set uid = user_id('dbo') Where name = …

    sp_configure 'Allow Update',0
    Reconfigure WITH OVERRIDE

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts