raiserror / terminate a users connection

Hi,
For slightly convoluted reasons I'd like to be able to terminate a users
connection from within a stored procedure, in a similar way as if I'd raised
an error with a severity of 20 or above. Is this possible?
The circumstances are that I have a number of security tests that I run at
various places, and there is one possible outcome which can only mean that a
user has been active for over 20 hours (practically impossible) or that the
system has been broken into in some way - I'd like to just completely kill
the connection immediately if this condition is detected, and wrap it in a
single stored procedure (i.e. take the key in question, test it, and then
raise an error and throw the user out if it fails).
Can it be done?
Thanks
NickYou can use KILL, but it won't be a neat error message for that connection..
.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Nick Stansbury" <nick.stansbury@.sage-removepartners.com> wrote in message
news:cutjph$rm0$1@.pop-news.nl.colt.net...
> Hi,
> For slightly convoluted reasons I'd like to be able to terminate a user
s
> connection from within a stored procedure, in a similar way as if I'd rais
ed
> an error with a severity of 20 or above. Is this possible?
> The circumstances are that I have a number of security tests that I run at
> various places, and there is one possible outcome which can only mean that
a
> user has been active for over 20 hours (practically impossible) or that th
e
> system has been broken into in some way - I'd like to just completely kill
> the connection immediately if this condition is detected, and wrap it in a
> single stored procedure (i.e. take the key in question, test it, and then
> raise an error and throw the user out if it fails).
> Can it be done?
> Thanks
> Nick
>|||But won't "Kill" only work for other connections other than the current
connection? What I need to do is Kill the current connection
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23MC5e55EFHA.2564@.tk2msftngp13.phx.gbl...
> You can use KILL, but it won't be a neat error message for that
connection...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "Nick Stansbury" <nick.stansbury@.sage-removepartners.com> wrote in message
> news:cutjph$rm0$1@.pop-news.nl.colt.net...
users
raised
at
that a
the
kill
a
then
>|||Sorry, I thought you want to execute this from the outside the connection, h
ence the reference to
KILL
I'm afraid that I can't see an easy way to accomplish this unless the user i
s symin. If so, you
can use RAISERROR with severity 20 or higher.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Nick Stansbury" <nick.stansbury@.sage-removepartners.com> wrote in message
news:cuv15k$al6$1@.pop-news.nl.colt.net...
> But won't "Kill" only work for other connections other than the current
> connection? What I need to do is Kill the current connection
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n
> message news:%23MC5e55EFHA.2564@.tk2msftngp13.phx.gbl...
> connection...
> users
> raised
> at
> that a
> the
> kill
> a
> then
>

Quick Transaction Question.

If in .NET I open a connection to my database, then use some sql text to start a transaction, then reuse that same open connection to call several stored procedures (using SqlCommand with CommandType.StoredProcedure), before ending the transaction. Will that run as a single transaction that can be rolled back? or are the stored procedure calls unable to roll-back after each one completes?

the stored procedures are themselves atomic and would need their own rollbacks. your use of transaction would work if all your statements were sql strings executing one after the other. -- jp

|||

Did a bit of research, and it appears there is a BeginTransaction method that can be used as part of the SqlConnection object. Which allowed me to start a transaction on a connection, then call serveral stored procedures using a SqlCommand object set to CommandType.StoredProcedure, and it all works as a single transaction, rolling everything back if any of then calls fail (internally or enternally). Here is a quick code snippet:

1protected void btnSave_Click(object sender, EventArgs e) {2//Create connection string for SQL query3 String strConnect;4 strConnect = WebConfigurationManager.ConnectionStrings["LocalSqlServer"].ConnectionString;56//Generate call to stored procedure7 SqlConnection con =new SqlConnection(strConnect);8 SqlTransaction trans =null;910//Make Calls11string myNull =null;12try {13 con.Open();14 trans = con.BeginTransaction();15int ret1 = spCall(con, trans,"two");16int ret3 = spCall(con, trans, myNull);17int ret2 = spCall(con, trans,"three");18 trans.Commit();19 Master.Message.CssClass ="Text_Message";20 Master.Message.Text = ret1.ToString();21 Master.Message.Visible =true;22 }23catch (Exception sql) {24 Master.Message.CssClass ="Text_Error";25 Master.Message.Text = sql.Message;26 Master.Message.Visible =true;27if (null != trans) {28 trans.Rollback();29 }30 }31finally {32 con.Close();33 con.Dispose();34 }35 }3637protected int spCall(SqlConnection myConn, SqlTransaction myTrans,string myParam) {3839//Generate call to stored procedure40 SqlCommand storedProcCommand =new SqlCommand("spTest", myConn);41 storedProcCommand.CommandType = CommandType.StoredProcedure;4243//Build SQL parameter list44 storedProcCommand.Parameters.AddWithValue("@.value", myParam);4546//Return code47 SqlParameter retParam = storedProcCommand.Parameters.Add("@.ReturnValue", SqlDbType.Int);48 retParam.Direction = ParameterDirection.ReturnValue;4950//Bind to transaction51 storedProcCommand.Transaction = myTrans;5253//Run stored procedure54int retCode = 0;55 SqlDataReader Reader = storedProcCommand.ExecuteReader();56 retCode = (int)storedProcCommand.Parameters["@.ReturnValue"].Value;57 Reader.Close();5859return retCode;60 }

 
1CREATE PROCEDURE [dbo].[spTest]2--Parameters3 @.valuevarchar(50) =null4AS56BEGIN78SET NOCOUNT ON;910INSERT INTO tbTest11 (12 [value]13 )14VALUES15 (16 @.value17 )1819RETURN@.@.Identity2021END
My first call inserts okay, the second call fails because nulls are not accepted by [value] in the table definition., and the third call never happens due to the exception thrown by the second call, which forced a rollback of the entire transaction, of which each stored procedure is a call of. Did a fair amount of testing and everything appears to be in order, if anyone notices anything I overlooked and or that could be problematic, please let me know.