Where I work we have a SQL Server database (Microsoft SQL Server 2008 R2) that serves as the back-end with two different user interfaces, a .NET web interface and a FoxPro interface.
Every month we need to apply updates to both the Web and FoxPro clients. Before doing this, we are advised to make sure that no one is accessing the database during the update process.
What it the easiest way to prevent access to the database while we update it? Follow-up question: What is the best way to prevent access while we update it?
Best Answer
There are couple of ways that you can restrict access to a database :
Keep database in single user mode using (Note this will be risky as if there is any other connection to the database then you might end up waiting or refused connection.)
You are better off using Option 2 as a safe and planned upgrade during your maintenance window.
EDIT:
Restricted user - only users with dbo rights on database allowed (e.g. db_owner, dbcreator, sysadmin). This means that multiple users can still be logged into the database, as long as they are DBO.
Single user - only one connection allowed i.e. first come, first served.
Due to the fact that Single user will be first come, first served -- it will be more risky in case of error or somehow your connections gets terminated.
When dealing with Logon Trigger, as @AaronBertrand pointed out, that it will not work for existing sessions, but you can over come that by first killing all the sessions and then enabling the trigger so that all the new incoming connections have to go through the trigger.
I can't think of any other way of restricting the connections to the database.