What are the challenges you face when working across database platforms? Take the survey
Options

Permission error

Brian DonahueBrian Donahue Posts: 6,590 Bronze 1
edited November 28, 2005 10:19AM in SQL Compare Previous Versions
Hello,

There are a few stored procedures in the master database that SQL Compare needs to run in order to collect information from the database. Another one is sp_executesql. There is no way around, I'm afraid, because this is done at the ADO .net level and we have no control over it because it's just the way that data access using the .net Framework takes place.

Comments

  • Options
    Brian DonahueBrian Donahue Posts: 6,590 Bronze 1
    Hi Greg,

    Changing the settings will cause SQL Compare to ignore the extended properties, however, it still needs to script them. SQL Compare absorbs everything from the database and stored it internally regardless of these settings.
  • Options
    Ok, so you're saying it ignores them but it really doesn't. Anyway...

    Back to my first question then:
    So is there no way to sync up a local database with one that is on a shared hosting site?

    I was able to do a SQL Data Compare between my local and the remote database, just not the SQL Compare.

    As long as I can sync the data, I think I will be ok but it would really be great if I could sync a remote database. Hypothetically, I don't see why it can't be done if I log into each server under the another login besides dbo. The user I am using has the role of Database Administrator, just not the built in dbo role.
  • Options
    Brian DonahueBrian Donahue Posts: 6,590 Bronze 1
    Hello Greg,

    It doesn't sound to me as if the DBO role is necessary, it's just that you need to execute some system stored procedures in the server's MASTER database.
  • Options
    OK, I'll buy that BUT...how do I get around that? Is it even possible?

    What I don't understand is that I can script out my database, go to Query Analyzer and run the script that creates all my tables, procs and UDTs just fine. So what is SQL Compare doing that it is trying to run something from the master database? I need it to NOT try to run sp_executesql or whatever it is trying to run.

    Can you please have someone look at this issue? I hate to think I paid for a tool I can't really use the way I need.
  • Options
    Brian DonahueBrian Donahue Posts: 6,590 Bronze 1
    Hello,

    SQL Compare still gets information about all of the objects in the database, regardless of what options you've set under the comparison options. This is a bit of a contrast to the older 2.x versions, which only scripted the objects that you have elected to compare.

    I can't see any way around this, as SQL Compare needs some information about logins from the master database in order to correctly script users.
  • Options
    @VERSION returns the information that SQL Compare needs about the database server and this information should be available to everybody...
This discussion has been closed.