How to kick everyone off SQL server

[Oliver1]

Hi,

How do I disable all remote connections to SQL server, but keep the local connection working.

[RobDog888]

By the local connection you mean like your connection while in enterprize manager?

If you mean remotely connected servers to your server then clear the Allow other SQL Servers to connect remotely to this SQL Server using RPC check box.

[Oliver1]

Sorry wasn't very clear at all.

I'm physically sat on the server (well not literally, as that would be silly) and for example I want to restore a backup copy of the database. To do this I want to remove all other connections bar my own, is there an easy way to do this? And then stop new connections being made.

[RobDog888]

Yes, there is but I dont have access to the Enterprize Manager interface right now as my host doesnt allow me admin permissions in it.

Perhaps a quick search at MS will turn up a KB Article.

Sorry, I too wish I had Admin access right know and I could find out easily.

[Shuja Ali]

Did you try looking at sp_configure procedure. There are two options that might help you user connections and remote access, although I have never tried this myself.

[RobDog888]

I came across that sp too but I know there is a easy way in EM to do it.
Do you have access to EM currently?

[Shuja Ali]

In EM, right click on the Server, select properties. IN the resulting dialog select Connections tab and in the bottom of the dialog ucheck "Allow other SQL Servers to connect remotely to this SQL Server using RPC".

@Rob: Is this what you are looking for?

[RobDog888]

Almost. That prevents other SQL servers from connecting but he needs to prevent and disconnect other connections to his db except for himself.

So if an app is connected he wants it to disconnect so he can make a backup. Although an online backup is ok to perform depending on the user load.

[szlamany]

If you are looking to not allow other users in the DB then

Right click on the DB in EM - select properties - go to the OPTIONS tab and

"restrict access" - you have two choices - Members of dbo... or Single User.

But I'm concerned about what you want to do - RESTORE a DB? Are you going to RESTORE a .BAK into an existing DB or drop the DB and restore the .BAK to make a new DB?

[techgnome]

I think I found it.... but it's not easy....
From EM, expand the following nodes:
_Your_Server_ -> Management -> Current Activity.
Click Current Activity, then right click on each of the current processes that are in your database and "Kill Process" ... however.... it's a dangerous thing to do. We do this alot around here and just find it easier to ask everyone to get out of the pool (so to speak) and then do the restore.

-tg

[Oliver1]

The database restore was more of an example.

The scenario is I tell everyone to get of the database for the next 10 minutes. But during this 10 minutes some numpty will try and gain a connection which I don't want, so short of unplugging the network cable how do I stop this.

[szlamany]

We put that control into our app from the start. It looks at a configuration table and can tell the users that the system is turned off at the moment or that the client app version level has changed - and they need the new version to continue.

Are you talking about a single client app at this point?

[oldbear]

Hi

Open the enterprise manager. Go to the properties dialog for the database. Select Restrict access/single user.

Now take the database offline (right click the database and you will get this option). This will force a checkpoint, keeping the integrity of your data. No users will lose data (as they may do if you do something drastic such as pulling out the network cable).

Bring it back online. Back it up (or whatever else you need to do). Now uncheck the Restrict access option.

Hope this helps

[kaffenils]

Hi,

How do I disable all remote connections to SQL server, but keep the local connection working.

Execute the following TSQL

ALTER DATABASE NAME_OF_DB_HERE SET SINGLE_USER WITH ROLLBACK

[Dave Sell]

Take the server off the network.