Assigning Object Ownership

jkeil

I use sqlcompare to generate an update script that I shell out to OSQL via command line in Jscript. When the database objects are created, they are assigned to me as the owner (ie [unicru/jkeil].[sproc]). The update script, however, prints this in the log "creating [dbo].[sproc]", but the actual create statement does "create procedure sproc". I want the create script to prepend the create proc statement with dbo so that I am not the owner in the database. What option will allow that to happen? Thanks for your help, JeffK

Brian Donahue

Hello Jeff,

There isn't any setting that you can use to change this behaviour. The problem is this: SQL Compare uses the SQL that was used to create the object to migrate it to another database. Although the SQL Server is aware of the true object owner internally, this information may not necessarily appear in the DDL that SQL Compare uses.

For instance, Enterprise Manager shows a stored procedure MyProc with an owner of DBO. Then you double-click the SP and see the creation DDL, which begins -

CREATE PROCEDURE MyProc

In this case SQL Compare will run this DDL, and SQL Server will assign ownership of the migrated object based on who is running the migration script. Of course, this will be DBO if the logged-in user is a member of the database's DBOwner role.

We're aware of this and will try to come up with a workaround. In the meantime, the only possibility is to qualify an owner to the stored procedure in the DDL by altering the stored procedure so that it begins with -

CREATE PROC [dbo].[MyProc]...

jkeil

Hi Brian,

Thanks for the reply...I figured that SQL Compare was generating the "Create" based on the database that it was referencing. Unfortunetly, sometimes the objects that I want to migrate over were not created with "dbo".

The workaround I came up with involved writing a console app to find all "Create" statements and add the prefix "dbo." to each one. It was necessary to do this rather than try to fix my SQL server role membership.

I would love to know how/when you come up with a programmatic workaround...

JeffK

Brian Donahue

Hello,

Thanks! Since we're only MSSQL only, we are in a good position to exploit the SQL Server management API to check what SQL Server 'thinks' the object is called and check it against the DDL. I'm not making any promises of a fix at this early stage, though...