3.9 and permissions to SQL 2008

edszumowski

I have a user who uses SQL Prompt to connect to a SQL 2005 server and has no problem.

When he attempts to use it with our SQL 2008 server (on which he has more permissions) he gets the following:

Unable to refresh or cache database [IP_ADDRESS].[DB_NAME] 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 [IP_ADDRESS].[DB_NAME] 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 )

What gives? He has the VIEW ANY DEFINITION right on the server and is a db_reader on the db in question.

dev_maniac

I have the same issue, connecting to 2008. My account is actually a db_owner, so really confused as to what permission I'm missing.

Can anyone help us out?

Ben Pinchen

Sorry that it's taken a while for someone to answer you. Could you try granting the user the VIEW SERVER STATE permission? Sometimes, if SQL Prompt needs to grab information regarding encryption keys this error can be returned if the user does not have this privilege.

edszumowski

That worked. Thanks for the answer.

Rgds,

Ed

dev_maniac

That was it. Thank you. For anyone else viewing this, I couldn't find a way to do that in SSMS. Here's the TSQL command.

GRANT VIEW SERVER STATE TO YourLogin

DennisRehm

I'm having the same problem. Unfortunately my database is on a hosted site and they do not wish to GRANT VIEW SERVER STATE for security reasons. Any suggestions?

Thanks.

wsoranno

dev_maniac wrote:

That was it. Thank you. For anyone else viewing this, I couldn't find a way to do that in SSMS. Here's the TSQL command.

GRANT VIEW SERVER STATE TO YourLogin

To set this permission in SSMS, right click the server in Object Explorer, then click Properties.
On the Server Properties window, click Permissions in the left nav.
The top pane has the list of Logins, scroll thru to find your login.
The bottom pane has the list of permissions. View Server State is the last in the list.