Schema (dbo) not scripted to all script files

jamal

I ran a comparison between a live database object and that same object in a script folder (that sql compare scripted out earlier). Both objects are owned by dbo. The scripted file's create command is:

Create Procedure dbo.ProcName

They compare the same when there are no other differences. However, when there are other differences between the server and the script file, the object name is marked as a difference. My biggest problem is if I script from the server to the script file, the dbo. is removed from the script file. Then, with our security setup, if I try to push that newly generated file to the same server it was scripted from the server forces the owner of the object to my windows account:

[Domain\userName].ProcName

Why is Sql Compare removing the dbo. from the scripted procedure name? Is there an option I haven't found to force this to stick?

Michelle T

If the stored procedure was originally created on the live database with 'CREATE PROCEDURE ProcName' rather than 'CREATE PROCEDURE dbo.ProcName' then it will end up scripted out as 'CREATE PROCEDURE ProcName'.

We've attempted to apply solutions to this a few times, but we're rather wary of actually editing people's stored procedures (which is what fixing this would basically be doing) because we don't want to assume that people didn't mean to create them like that...

jamal

I disagree with that position. What I'm trying to do is script a procedure from a server. The server is a 2000 server and the object is currently owned by dbo. If I script the object from the server and then turn around to run the just generated script on the server, the script is no longer owned by dbo and therefore not the same object. What SQL Compare is doing is potentially going back in time and restoring the past state of who owns an object, not who currently owns the object.

There are many cases where you can create/alter an object, then use GUI tools to change that objects owner. Would this still script as the original owner and not the current owner?

I think this should be an option in SQL Compare. It is the default for how SSMS scripts objects.[/i]

Michelle T

We try to patch up cases which have been renamed through the GUI - what we've not yet started to change is 'is this object's name fully qualified or not'. Sometimes we do accidentally change that in the course of fixing GUI renames, but mostly we try to preserve it.

I've added a link to this post to the existing feature request for such an option, so it's possible we'll pick it up to implement in future versions.

jamal

Thanks. I think this would really be a nice help to have the option of how to script the database object ownership.