Prevent users from using tables

Hi, is there a way to log users out of SQL server at the start of my critical path and then allow them to log in once it has completed.

We have issues where users are locking tables using MS Access and our batch waits. If the users have left for the day, then we have to manage the situation manually.


more ▼

asked Feb 25, 2014 at 08:49 AM in Default

avatar image

11 1 1 2

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first


OK, it's a heavyweight solution, forcibly disconnecting user sessions and rolling back any uncommitted transactions.

An example (from my post on bulk-restoring database backups):

 SELECT  @KillCommand = ''
 SELECT  @KillCommand = @KillCommand + 'KILL ' + CONVERT(CHAR(15), spid) + '   
 FROM    sysprocesses
 WHERE   dbid = DB_ID(<<my database name>>)
 IF @KillCommand <> '' 
         PRINT @KillCommand
         EXEC (@KillCommand)

more ▼

answered Feb 25, 2014 at 09:14 AM

avatar image

ThomasRushton ♦♦
42.2k 20 57 53


As you say it's a bit of a sledgehammer to crack a nut, but a good answer. I presume that I can select the SPID by user so I only log users out that are not in my team.

There's always more :O))

Thanks again.

Feb 25, 2014 at 09:38 AM Loading

Oh yes, you can put what you want in the SELECT statement that builds up the KILL list.

Feb 25, 2014 at 12:00 PM ThomasRushton ♦♦

Of course, there's nothing there that'll prevent the users from logging back in again and carrying on where they left off.

Feb 25, 2014 at 12:51 PM ThomasRushton ♦♦

I would just note that part of good user communication (which makes everyone's lives easier!) is in ensuring that the user gets a helpful message. For example, I'm not sure that Access wouldn't simply return an error message - and you can't necessarily see any record changes the user has not committed and would therefore lose. Personally, if I need to accomplish this in a "don't care about unsaved changes" method, I like setting a DB to single user mode with rollback immediate, but again a sledgehammer to crack a nut. It prevents re-login to the database, whilst the maintenance is carried out, but is at database level not table level. I'd strongly suggest end user education if possible.

Feb 25, 2014 at 10:06 PM Dave_Green ♦

Are the users performing updates with MS Access or are these just shared read locks that are blocking your process?

If MS Access is just reading, have you considered setting read_committed_snapshot on?

Outside of that, I like the DENY approach that @ThomasRushton suggested. If the users aren't supposed to connect at all during the time frame, the sledge hammer is looking even more attractive.

Feb 26, 2014 at 05:12 PM KenJ
(comments are locked)
10|1200 characters needed characters left

The users are just reading the data so no updates at all.

I will look at the read_commit_snapshot option.

I plan to use the kill command along with a polite pre kill email. Then a final email once the batch has comlpleted....how nice am I!!

If it continues, then I will find a larger sledgehammer.

Thanks All.

more ▼

answered Feb 28, 2014 at 11:29 AM

avatar image

11 1 1 2

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Feb 25, 2014 at 08:49 AM

Seen: 753 times

Last Updated: Feb 28, 2014 at 11:29 AM

Copyright 2018 Redgate Software. Privacy Policy