3.9 and permissions to SQL 2008

edszumowskiedszumowski Posts: 6 Bronze 2
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.

Comments

  • 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?
  • 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.
  • That worked. Thanks for the answer.

    Rgds,

    Ed
  • 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
  • DennisRehmDennisRehm Posts: 13 Bronze 2
    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.
  • 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.
    Thanks
    Bill Soranno
    MCP, MCTS, MCITP DBA
    Database Administrator
    Winona State University
    Maxwell 143
    [email protected]

    "Quality, like Success, is a Journey, not a Destination" - William Soranno '92
Sign In or Register to comment.