Recently a customer asked if SQL Compare and SQL Data Compare can be used with a read-only database as a source. It’s a good questions as I’ve seen some tools that create temp tables or do some other work in a source database, which might cause problems. Certainly someone running SQL Compare against production would want to ensure it works as a read-only application.
This post will look at SQL Compare with a read-write database but a user account with read-only access. My previous post looked at a read-only database.
This is part of a series of posts on SQL Compare.
Setup
I’ve got a couple of databases that I use for Compare demos. In this case, compare5_prod and compare1. The compare5_prod is set to read write, which is normal.
I created a new login, which will default to the Compare5_prod database.
This login maps to a user, which has read only rights in the database.
In my compare setup, I’ll use this login to connect to the database.
As you can see below, Compare works fine, even reading the various system metadata tables:
This works because SQL Compare is not writing anything to the database. We read metadata and then process that in-memory on the client before returning the results.
You can see this also works in SQL Data Compare. Here’s the connection:
And here are the results
Summary
This was a very simple example, but I find that clients always would prefer to see examples already completed and proof that something works when they are evaluating software. Hopefully this helps answer this question.
SQL Compare is an amazing tool that millions of users have enjoyed for 25 years. If you’ve never tried it, give it an eval today and see what you think.
Voice of the DBA 