Minumum Database Permissions required to Run SQL Compare

joetig

I am using SQL Compare 6 and a SQL 2005 database. Trying to perform comparison using limited [read-only] logins to prevent accidental updates.
Since the applications appears to go against the sysobjects table, I encounter an error
Msg 229, Level 14, State 5, Line 1
The SELECT permission was denied on the object 'sysobjects', database 'mssqlsystemresource', schema 'sys'.
Is there a minumum set of permissions that are necessary to perform the compare. I always script the changes and then run manually in SSMS.

Thanks
Joe

Anu D

Thank you for your post.

Comparing a database schema requires very little permissions. Any standard SQL user who belongs to the default PUBLIC role can do this. The only exception is comparing encrypted stored procedures on SQL Server 2000. In order to work around the encryption, the user must have SYSADMIN or SECURITYADMIN roles in order to decrypt the text.

Note that some SQL Server 2005 objects may have the VIEW DEFINITION permissions revoked for a user. It may be necessary to grant VIEW DEFINITION to your user for the database.

In SQL Server 2008, it may be necessary to grant VIEW SERVER STATE to access information about encryption keys, otherwise a "User does not have permission to perform this action" message may prevent comparison.

In order to synchronize the databases, the user must have modification permissions to the schema. The easiest way to grant this permission to a user without affecting any other security set on the database is to make the user a member of the db_ddladmin role.

Kindly let us know if you need more help on this.

joetig

I have granted view definition to the role assigned to the login and it still fails. When the was that big sql injection scare, we revoked the select against sysobjects to the public to prevent unauthorized. I believe that is the issue, so I granted select to sysobjects to the role and it worked

Anu D

Thanks for the update.
Brilliant it worked for you!!