August 8, 2005 at 4:11 am
Hi, maybe this problem was solved, I did'nt find it. When I am patching, or making some changes in database, I would like to deny access for users in our organisation. I have found one script to log off all already connected users, but I would like to deny access for not connected users. Because I kick off 15 users and next 20 are trying to log on in the same time. It would be nice, when it is possible to do so with one command or script. For my action I cannot stop entire server, only stop the traffic on server. thanx
August 8, 2005 at 4:55 am
Somethink like this might be what you need:
ALTER DATABASE <dbname> SET SINGLE_USER WITH ROLLBACK IMMEDIATE
go
Regards
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
August 8, 2005 at 1:56 pm
If you're trying to block users from connecting to (an instance of) SQL Server (as opposed to a single database), the quickest trick I've found to block logins is to change their passwords. Here's a brief overview on the steps involved:
For NT authentication, for a given login (whether user or group), run sp_denyLogin. To reverse this, issue sp_grantLogin.
For SQL Authentication, change the password. To reverse this, reset the password back to what it should be. (If you don't know the password... well, that could get tricky.)
This won't affect current connections, but it will prevent new connections from being made while the existing connections are closed down.
Philip
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply