1

We are using windows 2012 R2 server with Plesk and provide shared hosting for our customers.

Version Details: MSSQL server 2012 and SSMS 11.0.2100.60

In SSMS if we login with particular user, it is displaying all the database instead of particular user's database. Displaying all DBs to all users, it is a security issue. (though, they cant access the DB, Just displays the name).

How can I restrict it, to display only the DB which belongs to that specific user.

0

Using at least SQL Server Management Studio -16.3, you can right click on the Databases node in SSMS and add a filter to restrict displaying databases owned by that user.

enter image description here

  • Thank you, It might work for 16v, But we have a problem with SSMS 11.0.210. – Mani Apr 12 '18 at 13:02
  • In that case no 2 user can see the same db as only 1 db_owner is possible. I am afraid MS doesn't have a solution for this... You either see everything or you do not see anything ( Other than db_owner and sysadmin). – Ramakant Dadhichi Apr 12 '18 at 14:32
  • @RamakantDadhichi FYI the newest SSMS versions 17.x are fully compatible with SQL 2012, so there is no need to stay on the older version. The newer ones are much faster and more stable as well. – LowlyDBA Apr 13 '18 at 13:51
  • @LowlyDBA : That wasn't my comment brother :) – Ramakant Dadhichi Apr 13 '18 at 13:57
  • @RamakantDadhichi Woops, sorry! – LowlyDBA Apr 13 '18 at 14:02
0

In SQL Server restricting to view just one database is not possible. Either you see everything or you do not see anything (Except master and tempdb).Hiding all databases can be achieved by below steps :

Right Click the Instance in the Object explorer --> Properties--> Permissions--> Public --> Un-check the Grant : 'View any database '.

In this case only the db_owner and sysadmin can see the databases. db_owner can see only the one he owns while sysadmin can see all. Again giving db_owner privilege is not a good idea as at db level it is the highest privilege. Also one database can have only one owner. Hopefully MS will have a resolution for this soon.

  • Thanks, We have done as suggested. After that, None of the DB is displaying. – Mani Apr 12 '18 at 12:43
  • 1
    Yes right.Only db_owners/sysadmin will be able to view database in this case. Working on it to get the required result. will share if I am able to get it done. – Ramakant Dadhichi Apr 12 '18 at 14:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.