3.9 and permissions to SQL 2008

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 &#91;IP_ADDRESS&#93;.&#91;DB_NAME&#93; 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 &#91;IP_ADDRESS&#93;.&#91;DB_NAME&#93; due to the following error: The user does not have permission to perform this action.. ---&gt; System.Data.SqlClient.SqlException: The user does not have permission to perform this action.
   at System.Data.SqlClient.SqlConnection.OnError&#40;SqlException exception, Boolean breakConnection&#41;
   at System.Data.SqlClient.SqlInternalConnection.OnError&#40;SqlException exception, Boolean breakConnection&#41;
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning&#40;TdsParserStateObject stateObj&#41;
   at System.Data.SqlClient.TdsParser.Run&#40;RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj&#41;
   at System.Data.SqlClient.SqlDataReader.ConsumeMetaData&#40;&#41;
   at System.Data.SqlClient.SqlDataReader.get_MetaData&#40;&#41;
   at System.Data.SqlClient.SqlCommand.FinishExecuteReader&#40;SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString&#41;
   at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds&#40;CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async&#41;
   at System.Data.SqlClient.SqlCommand.RunExecuteReader&#40;CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result&#41;
   at System.Data.SqlClient.SqlCommand.RunExecuteReader&#40;CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method&#41;
   at System.Data.SqlClient.SqlCommand.ExecuteReader&#40;CommandBehavior behavior, String method&#41;
   at System.Data.SqlClient.SqlCommand.ExecuteReader&#40;&#41;
   at v.a&#40;String &#41;
   at v.ExecuteSqlSetting&#40;&#41;
   at o.aa&#40;&#41;
   at o.a&#40;n &#41;
   at RedGate.SQLCompare.Engine.Database.RegisterForSqlPrompt&#40;ConnectionProperties connectionProperties, Options options, Boolean includeSystemObjects, Boolean includeDependencies&#41;
   at cy.b&#40;IAuthenticationTokenProvider &#41;
   --- End of inner exception stack trace ---
   at cy.b&#40;IAuthenticationTokenProvider &#41;
   at cy.a&#40;IAuthenticationTokenProvider &#41;
   at cy.GetMetaData&#40;IAuthenticationTokenProvider provider&#41;
   at M.b&#40;G , IAuthenticationTokenProvider , Boolean , EventHandler`1 , EventHandler`1 , EventHandler`1 , EventHandler`1 &#41;

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

edszumowski November 24, 2008 15:15

Comments

6 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?

dev_maniac November 25, 2008 13:48

0
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.

Ben Pinchen January 06, 2009 10:13

0
That worked. Thanks for the answer.

Rgds,

Ed

edszumowski January 06, 2009 10:27

0
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

dev_maniac January 06, 2009 11:16

0
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.

DennisRehm March 04, 2009 12:31

0
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.

wsoranno May 13, 2009 10:41

0

Add comment

Please sign in to leave a comment.

How can we help you today?

3.9 and permissions to SQL 2008

Comments

Add comment