SQL Prompt - Unable to show database objects

Provides intelligent code completion for SQL Server editors.

Moderators: David Atkinson, Anu Deshpande, Luke Jefferson

SQL Prompt - Unable to show database objects

Postby randyv » Thu Sep 03, 2009 3:34 pm

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
randyv
 
Posts: 113
Joined: Wed Feb 28, 2007 3:27 pm

Postby Anu Deshpande » Thu Sep 03, 2009 3:57 pm

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
Anu Deshpande
 
Posts: 692
Joined: Mon Apr 20, 2009 3:53 pm
Location: Cambridge

grant what to who?

Postby randyv » Thu Sep 03, 2009 4:10 pm

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
randyv
 
Posts: 113
Joined: Wed Feb 28, 2007 3:27 pm

Postby Anu Deshpande » Thu Sep 03, 2009 4:19 pm

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
Anu Deshpande
 
Posts: 692
Joined: Mon Apr 20, 2009 3:53 pm
Location: Cambridge

Postby Anu Deshpande » Thu Sep 03, 2009 4:37 pm

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
Anu Deshpande
 
Posts: 692
Joined: Mon Apr 20, 2009 3:53 pm
Location: Cambridge

Yes, that worked

Postby randyv » Thu Sep 03, 2009 4:57 pm

The sproc worked.
What we do in life echoes in eternity <><
Randy Volters
randyv
 
Posts: 113
Joined: Wed Feb 28, 2007 3:27 pm

Postby Anu Deshpande » Fri Sep 04, 2009 8:28 am

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
Anu Deshpande
 
Posts: 692
Joined: Mon Apr 20, 2009 3:53 pm
Location: Cambridge

Postby dsheerin » Fri Jun 11, 2010 12:42 pm

I have had the same issue and this forum topic was of great help to me also - thanks guys!
dsheerin
 
Posts: 5
Joined: Thu Sep 25, 2008 5:03 pm
Location: Raleigh, North Carolina

Postby AndrewJacksonZA » Wed Oct 03, 2012 3:43 pm

Code: Select all
EXEC sp_ServerOption 'SERVER NAME', 'data access', 'true'
This worked for me, thanks Anu!
AndrewJacksonZA
 
Posts: 17
Joined: Wed May 05, 2010 11:09 am
Location: Johannesburg, South Africa


Return to SQL Prompt Previous Versions

Who is online

Users browsing this forum: No registered users and 0 guests