Configuring a SQL Server Database Available Only to Specific Users

Question

I am setting up a laptop for interviews with developers, ready for coding tests. I want to create a database for each candidate, but long before the interview. My preparation is as follows:

• Login as administrator.
• Create a local Windows user for each candidate.
• Create a default local SQL instance database for each candidate (with a name in the database name).

Thus, when I log on 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 often there is not enough time between interviews to back up / disconnect the previous candidate database and create the next candidate database (the machine is slow, so turning it off / on again, etc. takes time).

Is this possible, and if so, how?

Thank you very much in advance.

Answer 1

SQL Server 2005+ introduced an option that could suit your needs. It sounds as if your requirement is that the user does not see other databases in the object browser.

How to hide databases in SQL Server grants VIEW ANY DATABASE permission. This can be useful in this situation, when the X database should be hidden from all users other than the Windows X user.

This section of the MSDN forum (see the following posts) suggests that a combination of denial of user database representation and authorization is a possible solution.

I reproduced this on a SQL Server 2008 machine, but used SQL Server authentication in this quick test. It worked as described: all databases were hidden from the login name, except as indicated below.

 USE <customersdatabase> ALTER AUTHORIZATION ON DATABASE::<customerdatabase> to <customerlogin> USE MASTER DENY VIEW ANY DATABASE TO <customerlogin> 

Answer 2

With SQL Server, you can debug and attach databases. Thus, you can leave it registered as a regular and intermediate candidate, just separate the previous candidate database and attach the next one.

With previous Visual Studio projects, you can secure it with a password.

Answer 3

Log in to SSMS.

Click on the databases, click on the created database.

Deploy the security system, click "Users" and add the Windows login to this database. Make sure you do not add it to the server as a hole.

Then your candidates can log in using the integrated security system and look only at their database, or at least they can access only that.

Answer 4

Personally, I would do it a little differently - I would not create a separate Windows account for each candidate, instead I would use only one Windows account and set the SQL login for each. Cancel VIEW ANY DATABASE according to p.campbell's answer, and then just add each SQL login as a user to your corresponding database.

Thus, you can enter the system as each candidate arrives, all they need to do is connect to the SQL instance using the information you provided to enter the SQL system.