Drop all connections

Question

Drop all connections

Viewing 8 posts - 16 through 22 (of 22 total)

You must be logged in to reply to this topic. Login to reply

river1 SSC-Insane Points: 24309 More actions · Answer 1

I have tried to execute the procedure from Lowel and other procedure where i put all the databases in single user mode. Non of this procedures work in one case...

I have an app (visual basic app) this app will run the procedure sp_disconnect (from lowel) to disconnect all users from the SQL Server 2005 instance.

I have made a test, but it does not work as i espected....

I have a user (SQL Server user) named Pedro that is connected to a dababase through query Window in MS.

When i execute the procedure to terminate Pedro's connection it does not terminates the connection, because i try to delete Login Pedro but it raises an error...

Can someone help?

Lowell SSC Guru Points: 323518 More actions · Answer 2

ok just to be sure, you did uncomment this line like i said you needed to,right?

--EXEC SP_EXECUTESQL @STR

i didn't want other readers running that on production and getting in trouble.

other than that, you could try a cursor that runs the command alter database @dbname set single_user with rollback immediate on each database:

USE MASTER

declare

@isql varchar(2000),

@dbname varchar(64)

declare c1 cursor for select name from master..sysdatabases where name not in ('master','model','msdb','tempdb')

open c1

fetch next from c1 into @dbname

While @@fetch_status <> -1

begin

select @isql = 'alter database @dbname set single_user with rollback immediate'

select @isql = replace(@isql,'@dbname',@dbname)

print @isql

exec(@isql)

fetch next from c1 into @dbname

end

close c1

deallocate c1

GO

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Steve-3_5_7_9 SSChampion Points: 10832 More actions · Answer 3

river1 (3/30/2010)
The procedure that Lowel gave me is very usefull but i think that you are right , it's better to put all the databases in to single user mode and then (at the end) set the databases to multi user again.
Thank you.

After killing the connections, you can always disable the logins, in lieu of single user mode.

alter login xyz disable

river1 SSC-Insane Points: 24309 More actions · Answer 4

No need steve, i want to delete almost all the logins. But thanks

river1 SSC-Insane Points: 24309 More actions · Answer 5

river1

SSC-Insane

Points: 24309

March 31, 2010 at 6:19 am

#1143010

Lowel solution will solve my problem. Thank you

Brandie Tarvin SSC Guru Points: 173068 More actions · Answer 6

river1 (3/31/2010)
No need steve, i want to delete almost all the logins. But thanks

But disabling them first will guarantee you'll be able to delete them afterwards.

Can't logon to the system if your login is disabled. @=)

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

Steve-3_5_7_9 SSChampion Points: 10832 More actions · Answer 7

Well, you're not disabling your "admin" login; just the user logins. Anyway, the OP doesn't think my suggestion will work.

river1 SSC-Insane Points: 24309 More actions · Answer 8

In my case i think that i do not need that, but thanks