Question/Issue with User Defined Data types

kfilipowskykfilipowsky Posts: 14
edited December 12, 2006 2:42AM in SQL Toolkit Previous Versions
I have created a command line application used to promote select database objects between environment for developers. This is working very well with one exception.

I create a DB object for testing as follows:
sp_addtype @typename='testusertype', @phystype='datetime', @nulltype='not null',@owner='dbo'

It is a standard here that all objects must be owned by 'dbo'

When I migrate the user type with my utility, it is created on the target server but the owner is set to the Id running my command line utility, not dbo as it existed in its original form.

Following is an excerpt from the SQL generated by Redgate's API
sp_addtype N'testusertype', 'datetime', 'NOT NULL'

note the absense of @owner option. The source object is dbo.testusertype and target object ends up being filipowsky_adm.testusertype

Can you offer any advise here? If this an issue with the API or am I missing something in my code?

Karl Filipowsky
Pfizer Animal Health | Global Shared Services
Desk: 269-833-0990 | Cell: 734-216-4392 | Fax: 269-833-2255


  • Options
    Brian DonahueBrian Donahue Posts: 6,590 Bronze 1
    Hello Karl,

    I've had a look into this and I think it may be a design issue with the SQL Compare Engine. Because it does not fiddle with object ownership, migrating objects that were created with implicit ownership as the currently-logged-in-user, maybe it made more sense to strip the ownership from user-defined types as well. I'll raise this with the developers, but I have a feeling they are going to tell me the same thing.
  • Options
    It sounds simple enough to be to append a "@owner ='dbo'" to the SQL your API generates. Do you see any issues with this? I suppose I'll look into that this afternoon.
  • Options
    Brian DonahueBrian Donahue Posts: 6,590 Bronze 1
    It should be okay to do that -- provided the login you use can create objects as DBO...
  • Options
    OK, would you mind suggest the best place(class) to do what I suggested. The ExecutionBlock class doesn't have any 'set' accessors and wasn't really designed to have user's manipulating the SQL the API generates.

    if not, we'll just drop UDTs from the automation plan, we only have a few of those a year anyway.
  • Options
    Brian DonahueBrian Donahue Posts: 6,590 Bronze 1

    No, the executionblock is read-only. Either you need to make sure you connect to the database and execute the block with a user that's part of the DBO role, or save the script to a file and manipulate it and run it outside of the Toolkit.
Sign In or Register to comment.