Solved

Kill off access to sql server

Posted on 2015-01-12
5
131 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
5 Comments
 
LVL 18

Assisted Solution

by:SimonAdept
SimonAdept 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:
ScottPletcher 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:ScottPletcher
ID: 40545210
Temporarily disable the logins anyone else is using to get to the instance.
0
 
LVL 45

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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
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.
This video discusses moving either the default database or any database to a new volume.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

706 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now