Solved

Kill off access to sql server

Posted on 2015-01-12
5
143 Views
Last Modified: 2015-01-14
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
Comment
Question by:jamesmetcalf74
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 18

Assisted Solution

by:Simon
Simon earned 166 total points
ID: 40544977
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
 
LVL 69

Accepted Solution

by:
Scott Pletcher earned 167 total points
ID: 40545016
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
 

Author Comment

by:jamesmetcalf74
ID: 40545103
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
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 40545210
Temporarily disable the logins anyone else is using to get to the instance.
0
 
LVL 49

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 167 total points
ID: 40546293
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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Text file into sql server 5 42
database level memory cache..? 8 37
Getting local user timezone in Sql Server 5 30
how to make geography query faster?  SQL 7 45
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

749 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question