Grant Execute Fails

mzcopea

We use Red-gate to deploy Sql 2000 database changes from a Development environment to our other environments, exluding users and roles.

What we have run into is that if a developer is not DBO on the development database, but has Execute permissions to a specific Stored Proc, the "grant execute" step will fail with this error if a change has been made to the Stored Proc:
"There is no such user or group 'NAMEOFUSER'.".

The only way we have found around this is to make all developers DBO of the development database. Is this necessary?

Any ideas would be appreciated. Thank you.

JonathanWatts

Hi there mzcopea

Can you please verify that the developers exists in the target system?

Also I am assuming that you have disabled the include dependencies option to get this error?

If you wanted to avoid permissions being scripted there are the "ignore permissions" and "ignore user permissions and role membership" options which may help you.

Adopting a role based permissions system may also assist you in getting around this issue.

Thanks

Jonathan

mzcopea

hi jonathan,

no, the developers do not existing on the target system because we do not grant any permissions to the developers on other environments. are you saying that they need to exist on the target systems?

i should have explained that we are using the command-line sqlcompare. we have not used the "ignorepermissons" option because we thought that exluding /users and roles would exlude the permissions - would simply using the option "ignorepermissons" solve this problem?

i don't know how to disable the include dependencies, using the command-line. should we be doing this?

thanks for your help.
mzcopea

JonathanWatts

mzcopea,

Can I ask you what your command line string is, or for the contents of the xml argument file? Then I maybe able to come up with some suggestions for fixing your problem.

Many thanks,

Jonathan

mzcopea

here is our commandline script:

sqlcompare.exe /snapshot1:\d:\snap\namesnap.snp /server2:servername /database2:dbname /exclude:user /exclude:role /scriptfile:D:\scripts\Test\TestSynchronizationSql.sql /force /options:forcecolumnorder,ignorewhitespace


Thanks, Jonathan!

JonathanWatts

mzcopea,

Okay I will try to explain what is going on:

By using the /options flag you are overiding the default options one of which is "include dependencies".

Will usually suggest that this option is enabled to ensure that scripts don't fail because there are missing objects. This option always ensures that objects required by other objects are scripted and will pull in any dependent objects even if you have excluded them.

For example, A sp_adduser statement for your developer would be pulled into the script if you enabled this option because they have a permissions on objects.

Therefore you are probably right to disable this option.

Enabling the ignore permissions should remove the error, however if there are differences in permissions of users that that you have set up for your production database then these will not be migrated.

So your script should look like this:

sqlcompare.exe /snapshot1:\d:\snap\namesnap.snp /server2:servername /database2:dbname /exclude:user /exclude:role /scriptfile:D:\scripts\Test\TestSynchronizationSql.sql /force /options:forcecolumnorder,ignorewhitespace,IgnorePermissions