Kill user connections

[puterkrazy]

How can you programatically kill all SQL user connections using SQL?

 

[sunila7]

Hi,

Have a look at this link... Hope it is helpful

http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/Q_20628664.html

Sunil

 

[puterkrazy]

thanks, i looked at this but it is more involved than what i am looking for. is there a simple command to kill all users on a database?

 

[sunila7]

Hi,

Have a look at Alter Database command... u can set Database in a Single user mode...

ALTER DATABASE DBname SET SINGLE_USER

also u might want to look at Rollback option for this command...

ALTER DATABASE DBname SET SINGLE_USER ROLLBACK Immediate

Or

sp_dboption Dbname, 'Single User', True






Sunil

 

[puterkrazy]

so does setting to SET_SINGLE_USER autmatically kill all other user connections?

 

[sunila7]

Yes it will kill all the user connections...

Sunil

 

[puterkrazy]

I get this error:
Database state cannot be changed while other users are using the database 'db'

 

[sunila7]

Hi,

R u using SQL SERVER 7 ...Try this link.... This a stored procedure to kill all the connections in SQL 7

http://vyaskn.tripod.com/code/sp_dboption2.txt

Also have a look at this link

http://vyaskn.tripod.com/administration_faq.htm#q16

Hope it helps

Sunil