cache permission issue with 3.9 and 2008 r2

merk

I've tried specifying the SA login for this database, to no effect. I also tried specifying a false login for this database and i get the same results. So i'm wondering if sql prompt is actually using the login i provide or not.

The SA login should have sufficient permissions to cache this database, so i have no idea why it's failing.

As a test i installed the latest version of sql prompt, and it has no problem caching the database. So it seems like it might be a bug in 3.92 ?

Here's the full error message:

Unable to refresh or cache database [5.114.xxx.yyy].[xyz] due to the following error: The user does not have permission to perform this action..

RedGate.SqlPrompt.Engine.Cache.DatabaseRefreshException: Unable to refresh or cache database [5.114.xxx.yyy].[xyz] due to the following error: The user does not have permission to perform this action.. ---> System.Data.SqlClient.SqlException: The user does not have permission to perform this action.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()
at System.Data.SqlClient.SqlDataReader.get_MetaData()
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
at System.Data.SqlClient.SqlCommand.ExecuteReader()
at v.a(String )
at v.ExecuteSqlSetting()
at o.aa()
at o.a(n )
at RedGate.SQLCompare.Engine.Database.RegisterForSqlPrompt(ConnectionProperties connectionProperties, Options options, Boolean includeSystemObjects, Boolean includeDependencies)
at cy.b(IAuthenticationTokenProvider )
--- End of inner exception stack trace ---
at cy.b(IAuthenticationTokenProvider )
at cy.a(IAuthenticationTokenProvider )
at cy.GetMetaData(IAuthenticationTokenProvider provider)
at M.b(G , IAuthenticationTokenProvider , Boolean , EventHandler`1 , EventHandler`1 , EventHandler`1 , EventHandler`1 )

Anu D

Many thanks for our post and apologies for the inconvenience caused.

I can suggest you two things:

1. Make sure you are on latest SQL prompt 3 version: ftp://support.red-gate.com/patches/SQL_ ... .3.303.zip

2. And also for SQL 2008 servers it may be necessary to grant VIEW SERVER STATE explicitly to access information about encryption keys otherwise a 'User does not have permission to perform this action' message may prevent the display of candidates.

Kindly let me know if any of the above solves the issue you are having.