I am establishing a laptop for developer interviews ready for coding tests. I wish to produce a database for every candidate, but well prior to the job interview itself. My preparation goes such as this:
- Logon as admin.
- Produce a local Home windows user for every candidate.
- Produce a database around the local default demonstration of SQL server for every candidate (using their title within the database title).
So that after i logon to Home windows as each candidate, when they open SSMS they are able to only see their very own database within the Object Explorer.
I wish to get it done by doing this, since there frequently is not quite sufficient time in-between interviews to backup/remove the prior candidate's database and make the following candidate's database (the equipment is slowish, so logging off/on again etc needs time to work).
Is possible, and when so, how?
Thank you ahead of time.
It seems there's a choice in SQL Server 2005+ that may accommodate your requirements here. It may sound as though your requirement would be that the user does not see another databases in Object Explorer.
How To Hide Databases in SQL Server highlights the
VIEW ANY DATABASE permission. It might be helpful in cases like this where database X must be hidden all customers apart from Home windows user X.
This MSDN forum thread (see the bottom-most posts) indicates that a mix of denying the vista database to some user, and giving authorization is really a possible solution.
I have duplicated this on the SQL Server 2008 machine, but used SQL Server authentication within this quick test. It labored as referred to: all DBs were hidden in the login, save the main one specified below.
USE <customersdatabase> ALTER AUTHORIZATION ON DATABASE::<customerdatabase> to <customerlogin> USE MASTER DENY VIEW ANY DATABASE TO <customerlogin>
Sign in to SSMS.
Click databases, click the database you produced.
Expand security, click Customers, and add the home windows login for this database. Make certain you do not combine it with the server like a hole.
Your candidates can login using integrated security, and find out only their database, or at best they are able to only access that certain.
Personally i'd get it done slightly in a different way - i would not setup another Home windows take into account each candidate, rather i'd use only the one Home windows account and setup a SQL login for every. Revoke the
VIEW ANY DATABASE according to the solution from p.campbell, after which just add each SQL login like a user for their particular database.
By doing this you could have the equipment drenched in as each candidate arrives, all they need to do is connect with the SQL instance while using SQL login info you supply.
With SQL Server, you are able to detatch and fix databases. Which means you would depart it drenched in normally as well as in-between candidates, just detatch the prior candidates database, and fix the next.
Using the previous candidates Visual Studio project, you can zip up having a password.