What do do with different object owners

someprogrammer

The stored procedures in one database are owned by dbo, in the other by my user name with the ISP. How can I compare them?

ie.

[dbo].GetEverything

is showing up as a seperate stored procedure from

[mylogin].GetEverything

This is causing the whole thing to basically not work for this situation. Anybody have the solution?

Thanks!

Brian Donahue

Hello,

SQL Compare does not support comparing SQL Server objects with different ownership in this current version.

You can change the object ownership to match the other database using sp_changeobjectowner and compare the objects with SQL Compare. If the ownership is hard-coded into the creation DDL for the stored procedure (ie CREATE PROC [owner].[object] rather than CREATE PROC [object]), then there should be no effect when the script runs because it will be created with the proper ownership anyway. This could depend on the rights of the account that you connect to the SQL Server with, though, and you may get an error message instead.

Elja

I have the same problem.

It would be great if a function could be added that enables the user to ignore the owner information when comparing and creating tables (and other objects).

Elja

Elja

For others with the same problem; my solution was to let SQL Compare generate the script, but not run it.
Instead I saved the script to a file which I opened with a text editor.
I deleted all of the references to [mylogin]. and saved the script (replace function in text editor recommended ).

Next; run the script in the database you want to update.
This works fine.

Ofcourse this doesn't help with the comparing itself, but in my case it help alot.

Since this solution is simple, I hope this feature will be build in SQL Compare soon. .
Would save some time..

Elja

Brian Donahue

Hello Elja!

We are working on the ability to compare and synchronize different objects. If our plans turn out to be feasable, we should have this feature by mid 2006.

PizzaSlice

To get around the issue where SQL Compare doesn't compare two tables that are the same on two databases (like local and production), but are different according to SQL Compare because the owners are different (i.e. [dbo].[tblCompany] and [abc].[tblCompany]) I do the following:

Script the production database using SQL Enterprise Manager to a file on your local machine. Open the scripted file, find/replace all instances of [abc] and change it to [dbo]. Then, on the development database server, create a new database and run the script you just made. Now, you have two databases on your development machine:

dbApplication
dbApplication_live

both have the owner [dbo] on all the objects. Run SQL Compare between the two databases and you'll get what you're looking for.

I hope this helps ANYBODY out there. Boy, lot's of people are going to be upgrading when they come out with a new release!