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

No comments:

Post a Comment