I have a database server with several databases on it. I'd like to create a login for one of these databases, but when the user logs in to the server, I don't want the other databases on the server to show up. Is this possible?
I've created a login on the server and gave that login the db_readonly role on just one server. However, when I connect via SQL Server Managment Studio, I see a list of all of the databases. I can't see what tables are defined in each of them.
I want to remove this clutter so that when a user connects there is only one (or two if you include master) database available.
you may use the SSMS interface as following: -
1) Go to your SQL Server Instance, right click and select Properties.
2) Choose Permission on the left pane.
3) Select the specific user that you mention on the "Logins or roles" section.
4) At the permissions for section, check on Deny column for "View any database"
5) Go to the newly added databases, right click and select Properties.
6) Add the specific user under "Users or roles"
7) At the permissions for section, check on Grant column for "View database state" or any other permission that you would like to grant"