Databases not visible in SSMS 2008

• AmolNaik

  SSCarpal Tunnel

  Points: 4767

  More actions

  October 12, 2010 at 9:17 pm

  #241535

  Hi,

  I need quick help on one of the problems that i am trying to fix.

  We have SQL Server Enterprise environment and few user databases.There are couple users that have read access to one of the user database say "Test_ODS". These are SQL logins and were able to view all databases in the SSMS until this morning.

  This morning I was trying to restrict few other users from viewing other databases in the SSMS when they connect and i might have issued DENY VIEW command to [Public] role and that messed up the ability of the users to see databases in SSMS, but i am not certain could be some other command.

  Now although the users are able to connect to SQL Server but they are NOT able to view databases in the SSMS, all they see is system databases folder with master and temp db databases. The users can however issue USE Test_ODS command and query the tables and views.

  A point to note that Users with db owner permissions do NOT have this problem.

  Any ideas as to how to restore the ability of the SQL users to view databases in the SSMS explorer. Is there any system table, view that i need to grant access to public in order to make the databases visible in the SSMS.

  Tried every possible solution, i am at my wits endand still not able to figure out.

  Appreciate any help.

  Thanks in advance!

  Amol Naik

• nakkeeran.kannan

  Mr or Mrs. 500

  Points: 503

  More actions

  October 21, 2010 at 11:21 am

  #1240479

  Hi ,

  The VIEW ANY DATABASE permission should be granted to the role. By default it will be enabled.

  Thanks,

  Kannan