Professional Web Applications Themes

script to lock out users - Microsoft SQL / MS SQL Server

I'm am looking to find out if there is a way to write a script that will lock all but one user out of a database. We have a reporting database that if queried while the database is being updated would yield inacurate / incomplete results. logically, I can figure it out: If users not XYZ Then Kill users run job. I just don't know how to write it. I've used Books Online and a few other sources but can't seem to find syntax on how to kill user connections. Thanks in advance for any help. mrsparky...

  1. #1

    Default script to lock out users

    I'm am looking to find out if there is a way to write a
    script that will lock all but one user out of a database.
    We have a reporting database that if queried while the
    database is being updated would yield inacurate /
    incomplete results.

    logically, I can figure it out:

    If users not XYZ Then Kill users
    run job.

    I just don't know how to write it. I've used Books Online
    and a few other sources but can't seem to find syntax on
    how to kill user connections.

    Thanks in advance for any help.
    mrsparky
    mrsparky Guest

  2. #2

    Default script to lock out users

    declare sql varchar(100), spid int
    select spid = 0
    while spid < (select max(spid) from sysprocesses where
    dbid = mydbid)
    begin
    select spid = min(spid) from sysprocesses where dbid =
    mydbid and spid > spid
    select sql = 'kill ' + convert(varchar(10),spid)
    exec (sql)
    end

    Be careful not to kill system processes. How do you stop
    users re-connecting? This may not kill connections
    immediately.

    Might be better to set the database to dbo use only for
    the duration.
     
    database. 
    Online 
    Nigel Guest

  3. #3

    Default Re: script to lock out users

    Maybe settng the transaction isolation level on the connection to
    SERIALIZABLE would do the trick. This will likely create blocks, but killing
    SPIDS doesn't seem like a good thing.

    Bob Castleman
    SuccessWare Software


    "mrsparky" <com> wrote in message
    news:0a7501c35dad$89479100$gbl... 


    Bob Guest

  4. #4

    Default Re: script to lock out users

    Actually, you would *have* to put it in single user mode otherwise even
    after bumping everybody off, there would be nothing to prevent them from
    logging back in during the report generation.

    "Amy" <com> wrote in message
    news:phx.gbl... 
    >
    >[/ref]


    Bob Guest

  5. #5

    Default Re: script to lock out users

    You can set the database option 'single user' or 'restricted user' and you
    can also specify how to handle the users that are currently connected with
    the termination option. for example:

    ALTER DATABASE myDB SET SINGLE_USER WITH ROLLBACK AFTER 10 SECONDS

    Look in BOL 'ALTER DATABASE' for more details.

    You might want to consider seperating the reporting environment from the
    production environment instead of locking out the users.

    HTH

    "mrsparky" <com> wrote in message
    news:0a7501c35dad$89479100$gbl... 


    Amy Guest

Similar Threads

  1. Cannot lock the page to prevent other users from editingit. Please try again later. Error with ASP pages
    By mattffox in forum Macromedia Contribute Connection Administrtion
    Replies: 1
    Last Post: October 31st, 03:30 AM
  2. Changing users in php script
    By John in forum PHP Development
    Replies: 2
    Last Post: October 3rd, 07:08 AM
  3. Help needed to lock laptop down for users.
    By DCF in forum Windows Setup, Administration & Security
    Replies: 0
    Last Post: July 5th, 10:01 AM
  4. Multiple users sharing a file--need to lock out 2nd user
    By Kyra in forum Mac Applications & Software
    Replies: 1
    Last Post: June 28th, 10:25 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139