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?

4 Answers 4


There are couple of ways that you can restrict access to a database :

  1. Using LOGON Trigger - but only temporarily (enable before upgrade and then disable it). Refer to my answer here for more details including a script.
  2. You can shutdown IIS on the webservers so that no connections are made using the application. This is called "Applicaiton downtime"
  3. 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.)

    alter database databasename
    set single_user with rollback immediately

You are better off using Option 2 as a safe and planned upgrade during your maintenance window.


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.

  • We will definitely shut down IIS while performing the update, but I am not clear as to whether this will prevent logins from the FoxPro client. Logon Trigger might be the best solution. What do you think about RESTRICTED_USER mode rather than SINGLE_USER mode?
    – Baodad
    Jun 4, 2013 at 17:39
  • 2
    I think logon trigger is not necessarily a good way to go - existing sessions won't have to go through the trigger, so they may fire up more work even after you change the trigger to keep people out. Jun 4, 2013 at 17:52
  • @Baodad I have updated my answer with details.
    – Kin Shah
    Jun 4, 2013 at 18:35

If you put the database into Restricted User mode, then only members of the fixed server roles sysadmin or dbcreator, or members of the fixed database role db_owner can access the database:


If you want to force existing connections to be closed:


And when you're ready to put things back to normal:


Source: http://www.blackwasp.co.uk/SQLRestrictedUser.aspx

I don't recommend Single User mode in these cases, as the application update process may need to open multiple connections simultaneously.

  • Seems like an elegant solution, simpler than Logon triggers. I will need to see if it's right for our environment, or if there are ongoing jobs that will prevent us from entering RESTRICTED_USER mode.
    – Baodad
    Jun 4, 2013 at 17:49

If you have a set group of users that would access the database or just one or two application accounts that make the database connection you could simply disable the login(s) and kill all connections, and then just re-enable the login(s) when you are done.

  • Like, right click on the login in SSMS and 'disable?' I'll try that...
    – Baodad
    Jun 6, 2013 at 5:03
  • 1
    @Baodad - Right click, properties, status, Login Disabled
    – Cougar9000
    Jun 6, 2013 at 13:02
  • 1
    By golly, that might be just what I need. It's really only the main webuser account that I need to block access to to prevent main application access from users. Thanks!
    – Baodad
    Jun 6, 2013 at 17:55
  • @Cougar9000 by disabling the login, will that kill all existing connections? The way you phrase it seems like there might be an additional step after disabling the login in order to kill all connections. Can you confirm?
    – Chris
    Nov 3, 2014 at 16:06
  • @Chris you will have to kill the session after disabling the login. Disabling it prevents it from connecting again while you are trying to work, especially helpful with auto-relogin apps and such
    – Cougar9000
    Nov 11, 2014 at 20:25

I’m not sure I understand if your SQL Server is on the same machine as apps that are accessing it.

If not you can just disable firewall port for SQL Server for all incoming traffic and you’re all good. Another option is to disable IP address in SQL Server configuration manager that is used to access the server (and leave only local IP

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.