Permissions required by SqlDataCompare
ChrisClark
Posts: 7
Hi,
Can anyone tell me what sql permissions are required by SqlDataCompare to perform a comparison and produce (but not execute) a synchronisation script? I have a comparison failing with error code 77. Also, is it possible that this error is being caused by the user SqlDataCompare is running under not having permission for the file location that the script is being saved out to rather than insufficient priveleges to Sql?
Cheers,
Chris
Can anyone tell me what sql permissions are required by SqlDataCompare to perform a comparison and produce (but not execute) a synchronisation script? I have a comparison failing with error code 77. Also, is it possible that this error is being caused by the user SqlDataCompare is running under not having permission for the file location that the script is being saved out to rather than insufficient priveleges to Sql?
Cheers,
Chris
This discussion has been closed.
Comments
The obvious thing is that you need to have SELECT priviliges on the tables that you're comparing. You will also, however, need permissions to the schema for the database that you have selected. This should only require you to have the default PUBLIC role in the database. In SQL 2005, it is possible to deny users access to a specific SCHEMA; possibly this is the issue you're running into.
SQL Data Compare does also return error code 77 if the SQL file cannot be saved to disk because of permissions problems. The console output should help clarify what the problem really is, so using the OUT: argument to make a log of Data Compare may be a good idea to help track down the cause of the problem.
Thanks for your reply. About ten minute before your post I actually tracked this down (using filemon) to the C:\Program Files\Red Gate\SQL Bundle 5\RedGate.SQLDataCompare.Distribution.mod file that is created when SqlDataCompare runs. Unfortunately the package is running through the command line as a non-privileged user, and so doesn't have permissions to write to the Program Files folder. I have given the user the appropriate permissions but I would suggest that SqlDataCompare should not be writing out to privileged folders (it goes against Windows Logo guidelines!) - perhaps the All Users folder in Documents and Settings would be a better place for this file?
Cheers,
Chris
We'll certainly look into this -- please note that this particular issue only affects the command-line version of SQL Data Compare. The UI version already uses the %ALLUSERSPROFILE% folder for storing program licensing information.