Kill Session statement does not work

I logged in through query anayzer as system admin and then logged in a second session from a different user ID. From the sa window I issued "sp_who" and found the session id of 55 for the second logged in session. I then issued "KILL 55". When I reissued
"sp_who" it showed the session id was gone, but when I opened the user query analyzer window, I was still allowed to issue SELECT statements. Shouldnt the user session window close or leave some type of message to show that the session was logged out and
the window is no longer active?
> Shouldnt the user session window close or leave some type of message
> to show that the session was logged out and the window is no longer
active?
The actual behavior is that Query Analyzer will try to reestablish the
connection when you try to execute a query against a closed connection. You
can see this with a Profiler trace. Whether or not QA should notify the
user when this occurs is debatable.
Hope this helps.
Dan Guzman
SQL Server MVP
"Jack Wachtler" <jack_wachtler@.comcast.net> wrote in message
news:AA7FD110-6FD0-44D5-A011-EE320063ABC8@.microsoft.com...
> I logged in through query anayzer as system admin and then logged in a
second session from a different user ID. From the sa window I issued
"sp_who" and found the session id of 55 for the second logged in session. I
then issued "KILL 55". When I reissued "sp_who" it showed the session id was
gone, but when I opened the user query analyzer window, I was still allowed
to issue SELECT statements. Shouldnt the user session window close or leave
some type of message to show that the session was logged out and the window
is no longer active?

Kill Session statement does not work

I logged in through query anayzer as system admin and then logged in a secon
d session from a different user ID. From the sa window I issued "sp_who" and
found the session id of 55 for the second logged in session. I then issued
"KILL 55". When I reissued
"sp_who" it showed the session id was gone, but when I opened the user query
analyzer window, I was still allowed to issue SELECT statements. Shouldnt t
he user session window close or leave some type of message to show that the
session was logged out and
the window is no longer active?Hi,
Query analyzer will establish back the connection to SQL server on issuing
any SQL / DML statements.
Thanks
Hari
MCDBA
"Jack Wachtler" <jack_wachtler@.comcast.net> wrote in message
news:AA7FD110-6FD0-44D5-A011-EE320063ABC8@.microsoft.com...
> I logged in through query anayzer as system admin and then logged in a
second session from a different user ID. From the sa window I issued
"sp_who" and found the session id of 55 for the second logged in session. I
then issued "KILL 55". When I reissued "sp_who" it showed the session id was
gone, but when I opened the user query analyzer window, I was still allowed
to issue SELECT statements. Shouldnt the user session window close or leave
some type of message to show that the session was logged out and the window
is no longer active?|||> Shouldnt the user session window close or leave some type of message
> to show that the session was logged out and the window is no longer
active?
The actual behavior is that Query Analyzer will try to reestablish the
connection when you try to execute a query against a closed connection. You
can see this with a Profiler trace. Whether or not QA should notify the
user when this occurs is debatable.
Hope this helps.
Dan Guzman
SQL Server MVP
"Jack Wachtler" <jack_wachtler@.comcast.net> wrote in message
news:AA7FD110-6FD0-44D5-A011-EE320063ABC8@.microsoft.com...
> I logged in through query anayzer as system admin and then logged in a
second session from a different user ID. From the sa window I issued
"sp_who" and found the session id of 55 for the second logged in session. I
then issued "KILL 55". When I reissued "sp_who" it showed the session id was
gone, but when I opened the user query analyzer window, I was still allowed
to issue SELECT statements. Shouldnt the user session window close or leave
some type of message to show that the session was logged out and the window
is no longer active?

Kill

I need to let a third party security app run a script as a
system admin to drop and recreate a database. Before this
will run, of course I need to make sure all connections to
that database are dropped.
Is there a command that will kill all connections to a
database?Sometimes you just have to trace other programs that can do this. I traced
what happens when you disconnect a database and someone is using it.
select spid from master..sysprocesses where dbid=db_id('<database name>')
Then that spid result is fed to a kill statement.
Should warn you that this is a tricky thing that you are doing. Certain
kinds of connections, such as those with SQL Query Analyzer and Enterprise
Manager, do not drop very easily. Sometimes connections keep going. A
drastic step might be to use a net stop/net start to restart MSSQLserver.
That will certainly free up all the connections, though the database might
go into recovery.
But no, if the Clear connection button on the Detach Database function in
SQL EM doesn't call a command, I doubt you are going to find one.
--
*******************************************************************
Andy S.
MCSE NT/2000, MCDBA SQL 7/2000
andymcdba1@.NOMORESPAM.yahoo.com
Please remove NOMORESPAM before replying.
Always keep your antivirus and Microsoft software
up to date with the latest definitions and product updates.
Be suspicious of every email attachment, I will never send
or post anything other than the text of a http:// link nor
post the link directly to a file for downloading.
This posting is provided "as is" with no warranties
and confers no rights.
*******************************************************************
"gotit" <anonymous@.discussions.microsoft.com> wrote in message
news:00a701c3d3b6$6be8e6c0$a401280a@.phx.gbl...
> I need to let a third party security app run a script as a
> system admin to drop and recreate a database. Before this
> will run, of course I need to make sure all connections to
> that database are dropped.
> Is there a command that will kill all connections to a
> database?|||Add these lines to the top of the script.
ALTER DATABASE 'MyDBName' SET OFFLINE WITH ROLLBACK IMMEDIATE
GO
ALTER DATABASE 'MyDBName' SET ONLINE
GO
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
"gotit" <anonymous@.discussions.microsoft.com> wrote in message
news:00a701c3d3b6$6be8e6c0$a401280a@.phx.gbl...
> I need to let a third party security app run a script as a
> system admin to drop and recreate a database. Before this
> will run, of course I need to make sure all connections to
> that database are dropped.
> Is there a command that will kill all connections to a
> database?|||I know that I've seen a stored procedure on the net that will kill all
user connections. Try doing a search in google for something like "sp
kill all users" without the quotes.
Aaron
Andy Svendsen wrote:
> Sometimes you just have to trace other programs that can do this. I traced
> what happens when you disconnect a database and someone is using it.
> select spid from master..sysprocesses where dbid=db_id('<database name>')
> Then that spid result is fed to a kill statement.
> Should warn you that this is a tricky thing that you are doing. Certain
> kinds of connections, such as those with SQL Query Analyzer and Enterprise
> Manager, do not drop very easily. Sometimes connections keep going. A
> drastic step might be to use a net stop/net start to restart MSSQLserver.
> That will certainly free up all the connections, though the database might
> go into recovery.
> But no, if the Clear connection button on the Detach Database function in
> SQL EM doesn't call a command, I doubt you are going to find one.
>sql