Solved

Kill off access to sql server

Posted on 2015-01-12
5
150 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 50

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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

Audit has been really one of the more interesting, most useful, yet difficult to maintain topics in the history of SQL Server. In earlier versions of SQL people had very few options for auditing in SQL Server. It typically meant using SQL Trace …
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
In this video, viewers are given an introduction to using the Windows 10 Snipping Tool, how to quickly locate it when it's needed and also how make it always available with a single click of a mouse button, by pinning it to the Desktop Task Bar. Int…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…

728 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