Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase

SSMS object browser exception Expand / Collapse
Posted Sunday, November 16, 2008 6:44 PM

Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, December 1, 2016 4:22 PM
Points: 349, Visits: 1,385
Yesterday I fired up the 2008 SSMS for the first time since installing the enterprise evaluation. As it happened I actually needed it to help out a non SQL-guy colleague who was having some trouble with the SQL Server database behind their website.

To cut a long story short, the login with which I was provided did not have an associated user in all databases on the server (shared hosting). Therefore, when attempting to expand the "databases" folder in the SSMS object explorer, SSMS thew up an exception messagebox.

The key point here is that I should at least have been able to see the objects in our database even if I had no authority to the other databases on the server. But this did not happen. The object browser showed no objects at all.

I did set the connection's initial database to be our database, but this made no difference to the behaviour.

Is there any way I can browse the objects to which I do have permissions through SSMS object explorer without having permissions in every database on the server? In the end I resorted to querying the information_schema views, but for future reference a way to get the browser working would be nice.

Blog on sqlservercentral
Post #603407
Posted Sunday, November 16, 2008 8:47 PM



Group: Administrators
Last Login: Today @ 12:35 PM
Points: 34,363, Visits: 18,578
If you have rights in the database, you should be able to browse the objects.

You don't necessarily get these rights all the time. Someone could have removed rights to all schemas from public, which could cause problems.

Does this work fine against other SS2K8 instances? Thinking the client tools could be broken.

Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #603429
Posted Sunday, November 16, 2008 10:23 PM

Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, December 1, 2016 4:22 PM
Points: 349, Visits: 1,385
Thanks for your reply Steve.

The SSMS tool itself seems fine, in that I can browse all objects on my local server without a problem.

The error occurred when browsing a remote server, specifically a web hosting company's SQL Server, into which we login as a user with authority only over the database specific to my colleague's website, with no rights to the databases of any other websites which are also hosted on that sql server.

The details of the exception are as follows (cutting out the stack traces):

Failed to retrieve data for this request. (Microsoft.SqlServer.Management.Sdk.Sfc)

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

Server user 'user_name' is not a valid user in database 'not_our_database'. (.Net SqlClient Data Provider)

I should note that the object browser does work outside of the database list. That is to say, the management folders such as security, replication etc are all present. Further, the system databases folder is visible within the databases folder, and the system databases are visible within the system databases folder. But no other databases appear, including our database, so I can't graphically browse tables, procedures, etc.

Further consideration: The database server in question runs SQL Server 2000, I don't know whether or not SSMS is meant to be able to connect to an earlier version of SQL Server.

Blog on sqlservercentral
Post #603452
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse