Hiding databases for a login on Microsoft Sql Server 2008R2 and above

sql sql-server sql-server-2008 sql-server-2008-r2 sql-server-2012-express
Omgee Cares picture Omgee Cares · Jan 2, 2013 · Viewed 13.4k times · Source

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?

Answer

Raj picture Raj · Jan 2, 2013 
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

Related questions

Script to kill all connections to a database (More than RESTRICTED_USER ROLLBACK)

I have a development database that re-deploy frequently from a Visual Studio Database project (via a TFS Auto Build). Sometimes when I run my build I get this error: ALTER DATABASE failed because a lock could not be placed on …

Read More
Select SQL Server database size

how can i query my sql server to only get the size of database? I used this : use "MY_DB" exec sp_spaceused I got this : database_name database_size unallocated space My_DB 17899.13 MB 5309.39 MB It returns me several …

Read More
Using IF ELSE statement based on Count to execute different Insert statements

While I am searching through my database, I run an INSERT statement if I find that a particular item does not exist, and I run a different INSERT statement if I find one or more of this item. I am …

Read More