Using Visual Studio 2010 Schema and Data Compare against a read-only login.

Today I wanted to do a schema and data-compare against a (production) server where I only have a basic db_datareader access.

Ususally I employ the RedGate tools for tasks like this, but in this case I only succeded in getting the message “The user does not have permission to perform this action”.

image

Having spent ½ a day on testing on a another server I gave up: I was only able to get the RedGate tool to work if I was an administrator on the SQL server – something that wouldn’t happen in my real world. RedGate’s documentation didn’t help, and as I’m running their version 7 (they are now on 8 ) and SQL 2008R2 I decided to try a different approach.

I fired up the VS2010 tools and initially got similar “access denied” messages again. However, the RedGate documentation hinted that using the SQL command VIEW DEFINITION might solve the problem. While it solved nothing for the RedGate tools it actually made the VS2010 tools work! They are definitely not as slick as RedGate’s tools, but I’m here to get the job done so good enough is good enough.

The syntax loks like this:

USE master

GRANT VIEW ANY DEFINITION TO <mylogin> (see [1] below)

GRANT VIEW ANY DEFINITION TO PUBLIC (see [2] below)

USE <databasename>

GRANT VIEW DEFINITION TO PUBLIC

GRANT VIEW DEFINITION TO <mylogin> (see [3] below)

For more examples look at this article: Granting View Definition Permission to a User or Role in SQL Server

If you want to use MSSQL Server Management Studio the various types of VIEW DEFINITION are hidden in various places:

1) Right click on the server object > Properties > Permissions (sets access by login)

image

2) Open Server Roles > Right click “public” > Permissions (sets access by role)

image

3) Right click on a database > Properties > Permissions > Select a user > Find View definition > check Grant (sets access by database/login)

image

Advertisements
Posted in SQL

2 thoughts on “Using Visual Studio 2010 Schema and Data Compare against a read-only login.

  1. Peter Peart

    Hi there! Apologies that you are running into this issue, however the below pages on our website detail the minimum permissions required to use both SQL Compare and Data Compare:

    http://redg.at/cnqzKR

    http://redg.at/bQYI43

    If you these rights are set correctly and you would like us to work with you to resolve this, please do contact us on support(at)red-gate.com and mention this article.

    Thanks!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s