Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 205
  • Last Modified:

Kill off access to sql server

Whats the best way to accomplish two things?
for 2008 and 2012
1) kill sessions to the db.  I see in activity monitor I can kill processes.
2) disallow people to connect to a sql database, but... I don't want to kill the sql server service or any other related applications.  I just want to cut off the ability for people to login to the db
0
jamesmetcalf74
Asked:
jamesmetcalf74
3 Solutions
 
SimonCommented:
Set your databases to single user mode (kills user sessions without warning):
See the MSDN reference for doing this via SSMS

Very important that you read the link fully before doing this - and remember to switch back to multi-user when you're done.
0
 
Scott PletcherSenior DBACommented:
First try:

ALTER DATABASE <db_name> SET SINGLE_USER WITH ROLLBACK AFTER 4 SECONDS;

If that doesn't complete in a reasonable time -- say 15 secs -- cancel it and do this instead:

ALTER DATABASE <db_name> SET OFFLINE WITH ROLLBACK IMMEDIATE;

ALTER DATABASE <db_name> SET SINGLE_USER

ALTER DATABASE <db_name> SET ONLINE;

USE <db_name>

When ready for other users to access that db, run this command:

ALTER DATABASE <db_name> SET MULTI_USER


I strongly urge you to avoid using the GUI for tasks like this.  1) it's not reliable enough and 2) you can't easily repeat exactly what you did before, like you can with a script.
0
 
jamesmetcalf74Author Commented:
Thanks guys- I didn't post the question quite as clearly as I should have.
I'd like to kill access to the whole SQL instance, not just a specific database.
0
 
Scott PletcherSenior DBACommented:
Temporarily disable the logins anyone else is using to get to the instance.
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
I just want to cut off the ability for people to login to the db
Remotely? If so and if they don't connect through the port number but instance name then you can stop the SQL Server Browser service (the engine would keep working but no new connections allowed).
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now