SQL Compare with 2005 - sox issues - non dbo

TJayBelt

We have three segregated environments. Dev - UAT - Prod.

Developers can have read rights in the other two environments. And DBO or admin in their environment.

UAT (User Acceptance Test) can have read in prod and dev. But are admins in UAT.

The majority of our clients are still on SQL Server 2000. But we have some new ones on 2005. And we have noticed that if you are not dbo or admin on the db server, SQL Compare simply comes up blank with you do a compare. It allows you to connect, and choose database, but nothing comes up in the list of objects for comparison.

This means that an individual in one environment needs to be dbo in the lower environment in order to do a compare and roll that code... this is not good.

Is there something that we are doing wrong? Am i correct in the assumption that to do a compare, you need to be an admin or dbo?
How can we fix this, without having too much authority in the other environment?

thanks

JonathanWatts

Hey TJay,

Good to hear from you. Unfortunately we are limited by SQL Server's security, we need to query the system views and tables to build up the objects in the SQL Compare object model. If the user you are using to connect to the database hasn't the required permissions to read all the information about these objects then that object will not be displayed.

We recommend that if you are using SQL Compare on a database that you should be at least a dbo. However, I can understand that this can be limiting if you just want to compare databases, and not give users full rights to the database.

This is a reoccuring query, so I will press the developer concerned to come up with the minimum permissions required for SQL Compare.

Can I just ask how you are currently granting readonly access? Are you using the db_datareader role, or creating your own set of permissions?

Regards,

Jonathan

JonathanWatts

TJay,

I have been having a look at this since my last post, and of course on SQL 2005 there is the VIEW DEFINITION permission:

SQL 2005 Books Online wrote:

The VIEW DEFINITION permission lets a user see the metadata of the securable on which it is granted. However, VIEW DEFINITION permission does not confer access to the securable itself. For example, a user that is granted only VIEW DEFINITION permission on a table can see metadata related to the table in the sys.objects catalog view. However, without additional permissions, the user cannot read data from the table.

I tried this out with SQL Compare, and a database where my login was part of a role that only had the VIEW DEFINITION permission. I didn't have any problems, comparing all the db objects or generating change script.

Hope this helps,

Jonathan

TJayBelt

thanks for answering back so quickly. Unfortunately i never got the email that a post had been answered, and didnt look at this until now...

I will dig into the view definition and see what we can find out on this.

Thanks for all your help.