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”.
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:
GRANT VIEW ANY DEFINITION TO <mylogin> (see  below)
GRANT VIEW ANY DEFINITION TO PUBLIC (see  below)
GRANT VIEW DEFINITION TO PUBLIC
GRANT VIEW DEFINITION TO <mylogin> (see  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)
2) Open Server Roles > Right click “public” > Permissions (sets access by role)
3) Right click on a database > Properties > Permissions > Select a user > Find View definition > check Grant (sets access by database/login)