How can you hide databases you do not have access rights to when logging into SQL Server 2005 / 2008? Currently if a user connects, they see all the databases on the server, meaning they have to scan though the list to find their database.
+3
A:
You would need to revoke the permission 'VIEW ANY DATABASE' from the role PUBLIC (SQL SERVER 2005 onwards)
Mitch Wheat
2009-06-01 14:33:05
Note: this may not work for you if you can not make this user login the DBO of the database you want him to administer or if you have multiple logins you want to secure in this manner as only a single login can be the actual DBO of a database.
Mitch Wheat
2009-06-01 14:35:54
A:
There appears to be a server-side setting on MS SQL 2005 and 2008 to restrict the databases a user may see. I found the following text at sql-server-performance.com
In SQL Server 2005 it is possible with a new server side role that has been created. VIEW ANY DATABASE permission is a new, server-level permission. A login that is granted with this permission can see metadata that describes all databases, regardless of whether the login owns or can actually use a particular database. Please note By default, the VIEW ANY DATABASE permission is granted to the public role. Therefore, by default, every user that connects to an instance of SQL Server 2005 can see all databases in the instance.
Brettski
2009-06-01 14:33:54
+1
A:
This actually won't work the way that makes sense or that you might expect that it would. You REVOKE VIEW ANY DATABASE from the public role, but then the user has to be the database owner of the database or it can't be seen, but it still can be accessed. The problem is a Database Engine Security shortcoming and not likely to be fixed in the current or future release of SQL Server. Erland Sommarskog opened the following connect item for this a while ago, and it recently was discussed on twitter and with Microsoft by the SQL MVP's. Vote for the connect and help make it more of a priority for Microsoft to fix:
Basically the permissions are stored at the database level, so it would require enumerating each database to determine if the user has connect rights to display the database in the object explorer, which is an expensive task to perform and how the older EM used to do things. The proposes solution is for this information to be maintained at the server level as well, which is a major change.
Jonathan Kehayias
2009-06-01 17:59:35
+1
A:
After hours of trying to figure out how to create a user account which only has access to 1 DB, and can only see that DB. I think i figured it out!!!!
- Create a user account (make sure its not mapped to any Database)
- Right Click on the upper section of the SQL (SQLSERVER Name)>Properties>Permissions>Click on the user account, and select Deny to view databases.
- Right Click on the newly created DB, Properties,Files, and change the Owner to the newly created account.
At this point, once the user logs in he will see the Master,tempdb and will also see the new DB which he is a DB Owner of..You may want to go to Tools>Option and enabled the option to hide system objects so that you don't show the master,tempdb,etc. You may also need SP1 if this option does not work
Hope that helps...
Nikhil