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
jamesmetcalf74Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

 
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

Experts Exchange Solution brought to you by ConnectWise

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.