How can we help you today? How can we help you today?

SQL Prompt and permissions

Trying to write queries against production databases where I'm limited to read-only database user roels SQL Prompt generates various permissions errors as quoted below. This doe snot happen on development databases where I am in the SysAdmin role.
No permissions to decrypt objects

System.Exception: No permissions to decrypt objects ---> System.Data.SqlClient.SqlException: User 'FMER\meyer' does not have permission to run DBCC TRACEON.
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.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async)
at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at RedGate.SqlPrompt.PageReader.PromptPageReader..ctor(String conneString, ServerType serverType)
at RedGate.SqlPrompt.Cache.Candidates.DatabaseCandidateBase.a()
--- End of inner exception stack trace ---
No permissions to decrypt objects

System.Exception: No permissions to decrypt objects ---> System.Data.SqlClient.SqlException: User 'public' does not have permission to run DBCC DBINFO.
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.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async)
at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at RedGate.SqlPrompt.PageReader.PromptPageReader..ctor(String conneString, ServerType serverType)
at RedGate.SqlPrompt.Cache.Candidates.DatabaseCandidateBase.a()
--- End of inner exception stack trace ---
jmeyer
0

Comments

3 comments

Sort by
  • opc.three
    I am having the same issue. Can I make a suggestion red gate? Either make it so the escape button works to clear out these dialogs (less desirable) or have an option to not show exception dialogs at all and simply add them to an event log (most desirable).
    opc.three
    0
  • tanya
    Hello,

    Apologies for the inconvenience caused.

    Your suggestions are heard by Red Gate and the fix for this issue will be available in our next release scheduled for next week :)

    I hope that helps.

    Thanks,
    Tanya
    Project Manager
    Red Gate Software Ltd
    tanya
    0
  • opc.three
    tanya wrote:
    Hello,

    Apologies for the inconvenience caused.

    Your suggestions are heard by Red Gate and the fix for this issue will be available in our next release scheduled for next week :)

    I hope that helps.

    Thanks,
    Tanya
    Project Manager
    Red Gate Software Ltd

    Thank you for addressing this request in v4.0.1.2.
    opc.three
    0

Add comment

Please sign in to leave a comment.