Showing posts with label dependent. Show all posts
Showing posts with label dependent. Show all posts

Tuesday, February 14, 2012

Change ownership for User Defined Data Types

Hello,

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 youThere 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.|||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