November 16, 2008 at 6:44 pm
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.
November 16, 2008 at 8:47 pm
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.
November 16, 2008 at 10:23 pm
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.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply