Setup a SQL Server database that's only visible to certain users

Question

I'm setting up a laptop for developer interviews ready for coding tests. I want to create a database for each candidate, but well in advance of the interview itself. My preparation goes like this:

1. Logon as admin.
2. Create a local Windows user for each candidate.
3. Create a database on the local default instance of SQL server for each candidate (with their name in the database name).

Such that when I logon to Windows as each candidate, if they open SSMS they can only see their own database in the Object Explorer.

I want to do it this way, because there often isn't quite enough time in-between interviews to backup/detach the previous candidate's database and create the next candidate's database (the machine is slowish, so logging off/on again etc takes time).

Is this possible, and if so, how?

Many thanks in advance.

Answer 1

It appears there's an option in SQL Server 2005+ that could accommodate your needs here. It sounds as if your requirement is that the user doesn't see the other databases in Object Explorer.

How To Hide Databases in SQL Server highlights the VIEW ANY DATABASE permission. It may be useful in this situation where database X needs to be hidden from all users other than Windows user X.

This MSDN forum thread (see the bottom-most posts) suggests that a combination of denying the view database to a user, and giving authorization is a possible solution.

I've replicated this on a SQL Server 2008 machine, but used SQL Server authentication in this quick test. It worked as described: all DBs were hidden from the login, save the one specified below.

USE <customersdatabase>

ALTER AUTHORIZATION ON DATABASE::<customerdatabase> to <customerlogin>

USE MASTER

DENY VIEW ANY DATABASE TO <customerlogin>