Schema Retrieval Failed

jolthoff

Just upgraded to SQL Prompt 3.9... Had 3.6 before this. We just switched to using SQL 2008, thus the reason for the upgrade.

All my SQL 2008 users are getting the following errors (I say errors, because it's the same for each 2008 database we have, so only the database name changes):

Unable to refresh or cache database [DBCIDT\IDT].[master] 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 [DBCIDT\IDT].[master] 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(Exception , Database , IAuthenticationTokenProvider )
   at cy.a(IAuthenticationTokenProvider )
   at cy.GetMetaData(IAuthenticationTokenProvider provider)
   at M.b(G , IAuthenticationTokenProvider , Boolean , EventHandler`1 , EventHandler`1 , EventHandler`1 , EventHandler`1 )

Anyone know what the problem is. It seems to work fine on all our SQL 2005 databases.

Thanks
Jeff

Anu D

Thanks for your post.

Kindly grant the user following permission:
GRANT VIEW SERVER STATE TO <user>

Reason: For SQL Server 2008, it may be necessary to grant VIEW SERVER STATE to access information about encryption keys

Kindly let us know if this resolves your issue.

jolthoff

Thanks! I put a request in to get those permissions changed... I'll keep you posted!