Menu
Please can anyone assist with hiding the available databases on sql server 2008R2 or newer versions.
I have a new login user that I mapped to a specific database. When logging in with the specific login user I can see all the databases on the server, although I cannot access them except for the one I mapped to the login.
This is 100% but my problem is that I do not want the login to even see that those other databases are available.
How do I prevent those other databases that are not mapped to the login from displaying?
442
Try this: In SQL Server Management Studio, right click the server and click "Properties". Click on "Permissions" and then select the "Public" role and remove "Grant" from "View Any Database".
To hide an instance of the SQL Server Database EngineIn SQL Server Configuration Manager, expand SQL Server Network Configuration, right-click Protocols for <server instance>, and then select Properties. On the Flags tab, in the HideInstance box, select Yes, and then click OK to close the dialog box.
Use SQL Server Management StudioRight-click the database to change, and then select Properties. In the Database Properties dialog box, select the Options page. From the Restrict Access option, select Single. If other users are connected to the database, an Open Connections message will appear.
To do this launch SQL Server Configuration Manager and do the following: select the instance of SQL Server, right click and select Properties. After selecting properties you will get a window similar to the below image. In order to hide the name of the instance just set Hide Instance to "Yes" and click OK or Apply.
USE master;
GO
DENY VIEW ANY DATABASE TO [newlogin];
GO
USE yourDB;
GO
DROP USER newlogin;
GO
USE master;
GO
ALTER AUTHORIZATION ON DATABASE::yourDB TO [newlogin];
GO
Raj
103
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
