CODEWITHSUNDEEP
sql-serverdatabaseauthorization
Neil Barnwell
Neil Barnwell

Reputation: 42165

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

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.

Upvotes: 7

Views: 6987

Answers (4)

Simon Hughes
Simon Hughes

Reputation: 3574

With SQL Server, you can detatch and attach databases. So you could leave it logged in as normal and in-between candidates, just detatch the previous candidates database, and attach the next one.

With the previous candidates Visual Studio project, you could zip it up with a password.

Upvotes: 1

slugster
slugster

Reputation: 49985

Personally i would do it slightly differently - i wouldn't set up a separate Windows account for each candidate, instead i would use just the one Windows account and setup a SQL login for each. Revoke the VIEW ANY DATABASE as per the answer from p.campbell, and then just add each SQL login as a user to their respective database.

This way you can have the machine logged in as each candidate arrives, all they have to do is connect to the SQL instance using the SQL login info you supply.

Upvotes: 0

p.campbell
p.campbell

Reputation: 100627

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>

Upvotes: 5

Stefan Steiger
Stefan Steiger

Reputation: 82336

Log in to SSMS.

Click on databases, click on the database you created.

Expand security, click on Users, and add the windows login to this database. Make sure you don't add it to the server as a hole.

Then your candidates can login using integrated security, and see only their database, or at least they can only access that one.

Upvotes: 0

Related Questions