can i force disconnection from a stored proc ?

inside a stored proc
that validates the user,
can i force to disconnect ?
atte,
Hernn Castelo
SGA - UTN - FRBA
The only way I know of to force a disconnect is to raise an error with a
severity of 20 or higher. However, that requires that the login is a member
of the sysadmin role. Not a good idea, generally, to put everyone in that
role! Can't you just RETURN if the user isn't validated properly?
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
"Hernn Castelo" <bajopalabra@.hotmail.com> wrote in message
news:OZZ$grmzEHA.2012@.TK2MSFTNGP15.phx.gbl...
> inside a stored proc
> that validates the user,
> can i force to disconnect ?
> --
> atte,
> Hernn Castelo
> SGA - UTN - FRBA
>
|||Yes, it is possible with the Kill Spid command
Execute sp_who to get a report on valid SPID values. Use @.@.SPID to display
the value for the current session.
This example shows how to terminate SPID 10.
KILL 10
"Hernn Castelo" <bajopalabra@.hotmail.com> schreef in bericht
news:OZZ$grmzEHA.2012@.TK2MSFTNGP15.phx.gbl...
> inside a stored proc
> that validates the user,
> can i force to disconnect ?
> --
> atte,
> Hernn Castelo
> SGA - UTN - FRBA
>
|||And in addition to the sysadmin problem, try killing your own process and
see what happens:
Server: Msg 6104, Level 16, State 1, Line 1
Cannot use KILL to kill your own process.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
"Johan Koopmans" <koopmans.johan@.hccnet.nl> wrote in message
news:ecMX39mzEHA.1452@.TK2MSFTNGP11.phx.gbl...
> Yes, it is possible with the Kill Spid command
> Execute sp_who to get a report on valid SPID values. Use @.@.SPID to display
> the value for the current session.
> This example shows how to terminate SPID 10.
> KILL 10
|||You need to be a member of sysadmin to run KILL...
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
"Johan Koopmans" <koopmans.johan@.hccnet.nl> wrote in message
news:ecMX39mzEHA.1452@.TK2MSFTNGP11.phx.gbl...
> Yes, it is possible with the Kill Spid command
> Execute sp_who to get a report on valid SPID values. Use @.@.SPID to display
> the value for the current session.
> This example shows how to terminate SPID 10.
> KILL 10
>
|||Define validate and disconnect. I have my own definition.
Do you have English speaking people near you?
Jeff
"Hernn Castelo" <bajopalabra@.hotmail.com> wrote in message
news:OZZ$grmzEHA.2012@.TK2MSFTNGP15.phx.gbl...
> inside a stored proc
> that validates the user,
> can i force to disconnect ?
> --
> atte,
> Hernn Castelo
> SGA - UTN - FRBA
>
|||thanks for the replies
yes...you are right ...it is a bad idea
and not very useful
atte,
Hernn Castelo
SGA - UTN - FRBA
"Hernn Castelo" <bajopalabra@.hotmail.com> escribi en el mensaje
news:OZZ$grmzEHA.2012@.TK2MSFTNGP15.phx.gbl...
> inside a stored proc
> that validates the user,
> can i force to disconnect ?
> --
> atte,
> Hernn Castelo
> SGA - UTN - FRBA
>

can i force disconnection from a stored proc ?

inside a stored proc
that validates the user,
can i force to disconnect ?
atte,
Hernn Castelo
SGA - UTN - FRBAThe only way I know of to force a disconnect is to raise an error with a
severity of 20 or higher. However, that requires that the login is a member
of the sysadmin role. Not a good idea, generally, to put everyone in that
role! Can't you just RETURN if the user isn't validated properly?
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Hernn Castelo" <bajopalabra@.hotmail.com> wrote in message
news:OZZ$grmzEHA.2012@.TK2MSFTNGP15.phx.gbl...
> inside a stored proc
> that validates the user,
> can i force to disconnect ?
> --
> atte,
> Hernn Castelo
> SGA - UTN - FRBA
>|||Yes, it is possible with the Kill Spid command
Execute sp_who to get a report on valid SPID values. Use @.@.SPID to display
the value for the current session.
This example shows how to terminate SPID 10.
KILL 10
"Hernn Castelo" <bajopalabra@.hotmail.com> schreef in bericht
news:OZZ$grmzEHA.2012@.TK2MSFTNGP15.phx.gbl...
> inside a stored proc
> that validates the user,
> can i force to disconnect ?
> --
> atte,
> Hernn Castelo
> SGA - UTN - FRBA
>|||And in addition to the sysadmin problem, try killing your own process and
see what happens:
Server: Msg 6104, Level 16, State 1, Line 1
Cannot use KILL to kill your own process.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Johan Koopmans" <koopmans.johan@.hccnet.nl> wrote in message
news:ecMX39mzEHA.1452@.TK2MSFTNGP11.phx.gbl...
> Yes, it is possible with the Kill Spid command
> Execute sp_who to get a report on valid SPID values. Use @.@.SPID to display
> the value for the current session.
> This example shows how to terminate SPID 10.
> KILL 10|||You need to be a member of sysadmin to run KILL...
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Johan Koopmans" <koopmans.johan@.hccnet.nl> wrote in message
news:ecMX39mzEHA.1452@.TK2MSFTNGP11.phx.gbl...
> Yes, it is possible with the Kill Spid command
> Execute sp_who to get a report on valid SPID values. Use @.@.SPID to display
> the value for the current session.
> This example shows how to terminate SPID 10.
> KILL 10
>|||Define validate and disconnect. I have my own definition.
Do you have English speaking people near you?
Jeff
"Hernn Castelo" <bajopalabra@.hotmail.com> wrote in message
news:OZZ$grmzEHA.2012@.TK2MSFTNGP15.phx.gbl...
> inside a stored proc
> that validates the user,
> can i force to disconnect ?
> --
> atte,
> Hernn Castelo
> SGA - UTN - FRBA
>|||thanks for the replies
yes...you are right ...it is a bad idea
and not very useful
atte,
Hernn Castelo
SGA - UTN - FRBA
"Hernn Castelo" <bajopalabra@.hotmail.com> escribi en el mensaje
news:OZZ$grmzEHA.2012@.TK2MSFTNGP15.phx.gbl...
> inside a stored proc
> that validates the user,
> can i force to disconnect ?
> --
> atte,
> Hernn Castelo
> SGA - UTN - FRBA
>

can i force disconnection from a stored proc ?

inside a stored proc
that validates the user,
can i force to disconnect ?
--
atte,
Hernán Castelo
SGA - UTN - FRBAThe only way I know of to force a disconnect is to raise an error with a
severity of 20 or higher. However, that requires that the login is a member
of the sysadmin role. Not a good idea, generally, to put everyone in that
role! Can't you just RETURN if the user isn't validated properly?
--
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Hernán Castelo" <bajopalabra@.hotmail.com> wrote in message
news:OZZ$grmzEHA.2012@.TK2MSFTNGP15.phx.gbl...
> inside a stored proc
> that validates the user,
> can i force to disconnect ?
> --
> atte,
> Hernán Castelo
> SGA - UTN - FRBA
>|||Yes, it is possible with the Kill Spid command
Execute sp_who to get a report on valid SPID values. Use @.@.SPID to display
the value for the current session.
This example shows how to terminate SPID 10.
KILL 10
"Hernán Castelo" <bajopalabra@.hotmail.com> schreef in bericht
news:OZZ$grmzEHA.2012@.TK2MSFTNGP15.phx.gbl...
> inside a stored proc
> that validates the user,
> can i force to disconnect ?
> --
> atte,
> Hernán Castelo
> SGA - UTN - FRBA
>|||And in addition to the sysadmin problem, try killing your own process and
see what happens:
Server: Msg 6104, Level 16, State 1, Line 1
Cannot use KILL to kill your own process.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Johan Koopmans" <koopmans.johan@.hccnet.nl> wrote in message
news:ecMX39mzEHA.1452@.TK2MSFTNGP11.phx.gbl...
> Yes, it is possible with the Kill Spid command
> Execute sp_who to get a report on valid SPID values. Use @.@.SPID to display
> the value for the current session.
> This example shows how to terminate SPID 10.
> KILL 10|||You need to be a member of sysadmin to run KILL...
--
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Johan Koopmans" <koopmans.johan@.hccnet.nl> wrote in message
news:ecMX39mzEHA.1452@.TK2MSFTNGP11.phx.gbl...
> Yes, it is possible with the Kill Spid command
> Execute sp_who to get a report on valid SPID values. Use @.@.SPID to display
> the value for the current session.
> This example shows how to terminate SPID 10.
> KILL 10
>|||Define validate and disconnect. I have my own definition.
Do you have English speaking people near you?
Jeff
"Hernán Castelo" <bajopalabra@.hotmail.com> wrote in message
news:OZZ$grmzEHA.2012@.TK2MSFTNGP15.phx.gbl...
> inside a stored proc
> that validates the user,
> can i force to disconnect ?
> --
> atte,
> Hernán Castelo
> SGA - UTN - FRBA
>|||thanks for the replies
yes...you are right ...it is a bad idea
and not very useful
--
atte,
Hernán Castelo
SGA - UTN - FRBA
"Hernán Castelo" <bajopalabra@.hotmail.com> escribió en el mensaje
news:OZZ$grmzEHA.2012@.TK2MSFTNGP15.phx.gbl...
> inside a stored proc
> that validates the user,
> can i force to disconnect ?
> --
> atte,
> Hernán Castelo
> SGA - UTN - FRBA
>sql

Can I disconnect users on a timed basis?

I know SQL Server is a pretty powerful beast and I can use SQL Server Agent
Jobs to run things on a timed basis, but can anyone answer...
Is it possible to write scripts or stored procedures (or whatever is needed)
to disconnect a user from the database after a period of inactivity?
If so, can someone give me a clue how I would do this or possibly point me
in the right direction.
Thanks!Hi,
Yes it is possible to do, But please check the status of that user before
disconnecting. If it is runnable please wait for some more time and
kill the user once the status become "Sleeping"
declare @.status varchar(30)
declare @.pid int,@.sql nvarchar(100)
select @.status=status from master..sysprocesses where loginame='user_name'
if @.status = 'sleeping'
begin
select @.pid = spid from master..sysprocesses where loginame='user_name'
set @.sql= 'kill '+convert(char,@.pid)
exec sp_executesql @.sql
end
Schedule the script thru SQL Agent jobs. THe above script can take care of 1
user kill . If you have mutiple user, you have to slightly modify the script
Thanks
Hari
MCDBA
"Dave Roys" <daveroys@.xtra.co.nz> wrote in message
news:u4FeDTmAEHA.2600@.TK2MSFTNGP09.phx.gbl...
> I know SQL Server is a pretty powerful beast and I can use SQL Server
Agent
> Jobs to run things on a timed basis, but can anyone answer...
> Is it possible to write scripts or stored procedures (or whatever is
needed)
> to disconnect a user from the database after a period of inactivity?
> If so, can someone give me a clue how I would do this or possibly point me
> in the right direction.
> Thanks!
>