Solved

How to logout all user exept myself on a SQL-6.5 DB

Posted on 1999-01-26
6
1,081 Views
Last Modified: 2008-02-20
I need to find a way to logout all user exept myself from a SQL-6.5 Server DB in order to swtich the database temporally to 'single user'.
Does anybody have a procedure for that ?
0
Comment
Question by:scheuring
6 Comments
 
LVL 3

Expert Comment

by:jjbyers
ID: 1092847
Stop SQL Server, it will logout all users and then start in single user mode from the command-line.

Hope this helps.
0
 

Author Comment

by:scheuring
ID: 1092848
I can not stop the hole SQL-Server. I need a way to just logout all user on a specific database.
0
 
LVL 3

Expert Comment

by:jjbyers
ID: 1092849
use "sp_option dbname,dbo use only,true" and wait for everyone to get off.
Use "sp_option dbname,offline,true" to take it offline
use "sp_option dbname,single user,true" to open in single user mode.
0
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
LVL 2

Expert Comment

by:tschill120198
ID: 1092850
Are you looking for more immediate action, like a script that kills all spids?
0
 
LVL 2

Expert Comment

by:wpd
ID: 1092851
Use the 'Server Activity' option in SQL Entreprise Manager, and kill all the processes that access your db. Don't worry, only the connections will be broken :)
0
 

Accepted Solution

by:
lcsq earned 100 total points
ID: 1092852
Hi, guys,

all the info are in master..sysprocesses.

Just do this:
select 'kill '+convert(char(100),spid)+char(13)+char(10)+'go' from sysprocesses p,sysdatabases d
where d.name='your_db' and p.dbid=d.dbid

It will generate a script dynamically, and run it. But there are some risks. If some users log out after the script generated, you can kill the invalid users.

So in the production systems, be polite to users, and always call them directly.

Good luck!
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL 2014 always on 31 58
Run an action on recently added records to a table 13 60
sql server query 12 25
What is this datetime? 1 19
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

856 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