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:
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.
1) Login to SQL Management studio and connect to your SQL instance. 2) Expand Servers and select your SQL instance. Then tick the box Deny for "View any database" Please note that there are other ways of doing this, or by just setting a deny view permission on specific databases.
So SET RESTRICTED_USER will wait until all transactions have completed before taking affect. Once in place, regular users cannot issue further transactions, only members of the db_owner fixed database role and dbcreator and sysadmin fixed server roles can connect to the database.
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>
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With