Red Gate forums :: View topic - SQL Prompt - Unable to show database objects
Return to www.red-gate.com RSS Feed Available

Search  | Usergroups |  Profile |  Messages |  Log in  Register 
Go to product documentation
SQL Prompt Previous Versions
SQL Prompt Previous Versions forum

SQL Prompt - Unable to show database objects

Search in SQL Prompt Previous Versions forum
Post new topic   Reply to topic
Jump to:  
Author Message
randyv



Joined: 28 Feb 2007
Posts: 113

PostPosted: Thu Sep 03, 2009 3:34 pm    Post subject: SQL Prompt - Unable to show database objects Reply with quote

Don't know what to do about this (i.e., how do I fix it?)

SQL Prompt cannot retrieve objects from <snip>. Possible reasons:
- insufficient memory to cache objects
- invalid permissions for this database
- connection error
- corrupted database
Details:
Server '<snipservername> is not configured for DATA ACCESS.
_________________
What we do in life echoes in eternity <><
Randy Volters
Back to top
View user's profile Send private message Send e-mail
Anu Deshpande



Joined: 20 Apr 2009
Posts: 663
Location: Cambridge

PostPosted: Thu Sep 03, 2009 3:57 pm    Post subject: Reply with quote

Thanks for your post.


I guess it is a permissions issue.

Can you kindly try following permissions for that database from this knowledgebase here ?


Please let us know if this fixes the issue.
_________________
Anuradha Deshpande
Product Support
Redgate Software Ltd.
E-mail: support@red-gate.com
Back to top
View user's profile Send private message Send e-mail
randyv



Joined: 28 Feb 2007
Posts: 113

PostPosted: Thu Sep 03, 2009 4:10 pm    Post subject: grant what to who? Reply with quote

That support article says grant view server state as the only possible suggestion pertaining to SS2008, which is the system I'm getting this error on.

But the error description is not the one I am encountering so I doubt it will work, but I'm willing to try it - one issue; I'm logging in as 'sa' - doesn't 'sa' have this permission by default?

Just makes no sense.
_________________
What we do in life echoes in eternity <><
Randy Volters
Back to top
View user's profile Send private message Send e-mail
Anu Deshpande



Joined: 20 Apr 2009
Posts: 663
Location: Cambridge

PostPosted: Thu Sep 03, 2009 4:19 pm    Post subject: Reply with quote

Thanks for your reply.

I think you should try granting following rights:

1. GRANT VIEW DEFINITION TO [{username}]

2. GRANT VIEW SERVER STATE TO [{username}]

Please let us know if that helped.
_________________
Anuradha Deshpande
Product Support
Redgate Software Ltd.
E-mail: support@red-gate.com
Back to top
View user's profile Send private message Send e-mail
Anu Deshpande



Joined: 20 Apr 2009
Posts: 663
Location: Cambridge

PostPosted: Thu Sep 03, 2009 4:37 pm    Post subject: Reply with quote

If the above solution doesn't work and still you are getting error than your SQL Server needs to be set accordingly.

Can you try executing below query in context of MASTER database:

exec sp_serveroption 'SERVER NAME', 'data access', 'true'

Kindly let us know if this fixes the issue.
_________________
Anuradha Deshpande
Product Support
Redgate Software Ltd.
E-mail: support@red-gate.com
Back to top
View user's profile Send private message Send e-mail
randyv



Joined: 28 Feb 2007
Posts: 113

PostPosted: Thu Sep 03, 2009 4:57 pm    Post subject: Yes, that worked Reply with quote

The sproc worked.
_________________
What we do in life echoes in eternity <><
Randy Volters
Back to top
View user's profile Send private message Send e-mail
Anu Deshpande



Joined: 20 Apr 2009
Posts: 663
Location: Cambridge

PostPosted: Fri Sep 04, 2009 8:28 am    Post subject: Reply with quote

Brilliant!!

Thanks for the update.

Can you kindly let us know which of the solution provided by us solved the issue?
_________________
Anuradha Deshpande
Product Support
Redgate Software Ltd.
E-mail: support@red-gate.com
Back to top
View user's profile Send private message Send e-mail
dsheerin



Joined: 25 Sep 2008
Posts: 4
Location: Raleigh, North Carolina

PostPosted: Fri Jun 11, 2010 12:42 pm    Post subject: Reply with quote

I have had the same issue and this forum topic was of great help to me also - thanks guys!
Back to top
View user's profile Send private message
AndrewJacksonZA



Joined: 05 May 2010
Posts: 17
Location: Johannesburg, South Africa

PostPosted: Wed Oct 03, 2012 3:43 pm    Post subject: Reply with quote

Code:
EXEC sp_ServerOption 'SERVER NAME', 'data access', 'true'
This worked for me, thanks Anu!
Back to top
View user's profile Send private message
Display posts from previous:   
Reply to topic All times are GMT + 1 Hour
Page 1 of 1

 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group