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
>

raiserror

Hi
I want to make a trigger that prevent users from deleting rows that they
didnt insert and make en raiseror that tells them why the couldnt remove the
row, the table contains a column that says what sql login made the insert'
does anyone have an idea?you can do this in two ways.
1. write a query such that u delete only the rows the user inserted:
DELETE FROM <TABLE> WHERE user_ID = user
or
2.
In the trigger, check the deleted table
IF NOT EXISTS(SELECT * FROM DELETED where user_id = user)
please let me know if u have any questions
best Regards,
Chandra
http://chanduas.blogspot.com/
http://www.SQLResource.com/
---
"LeSurfer" wrote:

> Hi
> I want to make a trigger that prevent users from deleting rows that they
> didnt insert and make en raiseror that tells them why the couldnt remove t
he
> row, the table contains a column that says what sql login made the insert?
?
> does anyone have an idea?|||Hi
I don′t want the trigger to delete any rows, i want the trigger to prevent
users from deleteing rows which they ditn′t insert.
I have a program with a table with customers, when a new customer is created
it also inserts a column with the sql user login id name of the person how
made the insert. Now i want to make a trigger, so that you can only remove
rows which you inserted!
Fredrik
"Chandra" wrote:
> you can do this in two ways.
> 1. write a query such that u delete only the rows the user inserted:
> DELETE FROM <TABLE> WHERE user_ID = user
> or
> 2.
> In the trigger, check the deleted table
> IF NOT EXISTS(SELECT * FROM DELETED where user_id = user)
> please let me know if u have any questions
>
> --
> best Regards,
> Chandra
> http://chanduas.blogspot.com/
> http://www.SQLResource.com/
> ---
>
> "LeSurfer" wrote:
>|||WHERE user_ID = user is the key as Chandra stated.
You can only delete the rows that match the currently logged in user.
Whatever logic you are using to delete the row in the trigger then add the
user_ID = user condition. Therefore if dbo logs in and tries to delete rows
'LeSurfer' entered he will not be able coz it wont meet the condition.
Your raiserror can be scripted as follows:
GOTO ERRORPOINT
ERROR_POINT:
raiserror(@.text,16,-1)
ROLLBACK TRANSACTION
GOTO FINISH
FINISH:
End
"LeSurfer" wrote:
> Hi
> I don′t want the trigger to delete any rows, i want the trigger to preven
t
> users from deleteing rows which they ditn′t insert.
> I have a program with a table with customers, when a new customer is creat
ed
> it also inserts a column with the sql user login id name of the person how
> made the insert. Now i want to make a trigger, so that you can only remove
> rows which you inserted!
> Fredrik
> "Chandra" wrote:
>|||On Tue, 6 Sep 2005 03:25:02 -0700, LeSurfer wrote:

>Hi
>I want to make a trigger that prevent users from deleting rows that they
>didnt insert and make en raiseror that tells them why the couldnt remove th
e
>row, the table contains a column that says what sql login made the insert'
>does anyone have an idea?
Hi LeSurfer,
Something like this, maybe?
CREATE TRIGGER MyTrigger
ON MyTable FOR DELETE
AS
IF EXISTS (SELECT *
FROM deleted
WHERE TheColumnWithTheUserID <> CURRENT_USER)
BEGIN
RAISERROR ('You can''t delete rows that were inserted by someone
else', 16, 1)
ROLLBACK TRANSACTION
END
go
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Perfekt, thanks a lot.........
"Hugo Kornelis" wrote:

> On Tue, 6 Sep 2005 03:25:02 -0700, LeSurfer wrote:
>
> Hi LeSurfer,
> Something like this, maybe?
> CREATE TRIGGER MyTrigger
> ON MyTable FOR DELETE
> AS
> IF EXISTS (SELECT *
> FROM deleted
> WHERE TheColumnWithTheUserID <> CURRENT_USER)
> BEGIN
> RAISERROR ('You can''t delete rows that were inserted by someone
> else', 16, 1)
> ROLLBACK TRANSACTION
> END
> go
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>

RAID 1 Configuration

We are going to deploy a SQL Server 2000 for an
application which information is static. We just provide
queries for end users.
In order to minimize the backup procedure, we would like
to use RAID 1 mirroring configuration. Is this choice OK ?
Your backup procedure has nothing to do with your choice of RAID
configuration (unless I'm missing something here). Performance and
Redundancy has.
Aramid
On Wed, 6 Apr 2005 22:50:08 -0700, "Peter"
<anonymous@.discussions.microsoft.com> wrote:

>We are going to deploy a SQL Server 2000 for an
>application which information is static. We just provide
>queries for end users.
>In order to minimize the backup procedure, we would like
>to use RAID 1 mirroring configuration. Is this choice OK ?
|||We will make a backup of whole drive once a week as the
data is static.
The reason why we think RAID 1 is because we think that if
one of the disk breaks down, the other one still works
properly.
Re the performance issue, we would like to know what is
the pros and cons of RAID 1.
Thanks

>--Original Message--
>Your backup procedure has nothing to do with your choice
of RAID
>configuration (unless I'm missing something here).
Performance and[vbcol=seagreen]
>Redundancy has.
>Aramid
>On Wed, 6 Apr 2005 22:50:08 -0700, "Peter"
><anonymous@.discussions.microsoft.com> wrote:
provide[vbcol=seagreen]
OK ?
>.
>
|||Hi
The more spindles you have, the better your performance.
RAID-1 is good for performance and tolerance to failure, but RAID-10 (Or
0+1) is much better as it is striped and mirrored. So, you have the benefit
of Mirroring, plus you have multiple drives doing requests at the same time.
SCSI, not IDE or SATA.
Hardware mirroring, not software.
RAID-5 is evil, don't go there.
SQL Serrver does it's IO in 64k blocks (8 extents of 8k pages), so format
NTFS using 64Kb blocks.
Regards
Mike
"Peter" wrote:

> We will make a backup of whole drive once a week as the
> data is static.
> The reason why we think RAID 1 is because we think that if
> one of the disk breaks down, the other one still works
> properly.
> Re the performance issue, we would like to know what is
> the pros and cons of RAID 1.
> Thanks
> of RAID
> Performance and
> provide
> OK ?
>

RAID 1 Configuration

We are going to deploy a SQL Server 2000 for an
application which information is static. We just provide
queries for end users.
In order to minimize the backup procedure, we would like
to use RAID 1 mirroring configuration. Is this choice OK ?Your backup procedure has nothing to do with your choice of RAID
configuration (unless I'm missing something here). Performance and
Redundancy has.
Aramid
On Wed, 6 Apr 2005 22:50:08 -0700, "Peter"
<anonymous@.discussions.microsoft.com> wrote:

>We are going to deploy a SQL Server 2000 for an
>application which information is static. We just provide
>queries for end users.
>In order to minimize the backup procedure, we would like
>to use RAID 1 mirroring configuration. Is this choice OK ?|||We will make a backup of whole drive once a week as the
data is static.
The reason why we think RAID 1 is because we think that if
one of the disk breaks down, the other one still works
properly.
Re the performance issue, we would like to know what is
the pros and cons of RAID 1.
Thanks

>--Original Message--
>Your backup procedure has nothing to do with your choice
of RAID
>configuration (unless I'm missing something here).
Performance and
>Redundancy has.
>Aramid
>On Wed, 6 Apr 2005 22:50:08 -0700, "Peter"
><anonymous@.discussions.microsoft.com> wrote:
>
provide[vbcol=seagreen]
OK ?[vbcol=seagreen]
>.
>|||Hi
The more spindles you have, the better your performance.
RAID-1 is good for performance and tolerance to failure, but RAID-10 (Or
0+1) is much better as it is striped and mirrored. So, you have the benefit
of Mirroring, plus you have multiple drives doing requests at the same time.
SCSI, not IDE or SATA.
Hardware mirroring, not software.
RAID-5 is evil, don't go there.
SQL Serrver does it's IO in 64k blocks (8 extents of 8k pages), so format
NTFS using 64Kb blocks.
Regards
Mike
"Peter" wrote:

> We will make a backup of whole drive once a week as the
> data is static.
> The reason why we think RAID 1 is because we think that if
> one of the disk breaks down, the other one still works
> properly.
> Re the performance issue, we would like to know what is
> the pros and cons of RAID 1.
> Thanks
>
> of RAID
> Performance and
> provide
> OK ?
>

RAID 1 Configuration

We are going to deploy a SQL Server 2000 for an
application which information is static. We just provide
queries for end users.
In order to minimize the backup procedure, we would like
to use RAID 1 mirroring configuration. Is this choice OK ?Your backup procedure has nothing to do with your choice of RAID
configuration (unless I'm missing something here). Performance and
Redundancy has.
Aramid
On Wed, 6 Apr 2005 22:50:08 -0700, "Peter"
<anonymous@.discussions.microsoft.com> wrote:
>We are going to deploy a SQL Server 2000 for an
>application which information is static. We just provide
>queries for end users.
>In order to minimize the backup procedure, we would like
>to use RAID 1 mirroring configuration. Is this choice OK ?|||We will make a backup of whole drive once a week as the
data is static.
The reason why we think RAID 1 is because we think that if
one of the disk breaks down, the other one still works
properly.
Re the performance issue, we would like to know what is
the pros and cons of RAID 1.
Thanks
>--Original Message--
>Your backup procedure has nothing to do with your choice
of RAID
>configuration (unless I'm missing something here).
Performance and
>Redundancy has.
>Aramid
>On Wed, 6 Apr 2005 22:50:08 -0700, "Peter"
><anonymous@.discussions.microsoft.com> wrote:
>>We are going to deploy a SQL Server 2000 for an
>>application which information is static. We just
provide
>>queries for end users.
>>In order to minimize the backup procedure, we would like
>>to use RAID 1 mirroring configuration. Is this choice
OK ?
>.
>|||Hi
The more spindles you have, the better your performance.
RAID-1 is good for performance and tolerance to failure, but RAID-10 (Or
0+1) is much better as it is striped and mirrored. So, you have the benefit
of Mirroring, plus you have multiple drives doing requests at the same time.
SCSI, not IDE or SATA.
Hardware mirroring, not software.
RAID-5 is evil, don't go there.
SQL Serrver does it's IO in 64k blocks (8 extents of 8k pages), so format
NTFS using 64Kb blocks.
Regards
Mike
"Peter" wrote:
> We will make a backup of whole drive once a week as the
> data is static.
> The reason why we think RAID 1 is because we think that if
> one of the disk breaks down, the other one still works
> properly.
> Re the performance issue, we would like to know what is
> the pros and cons of RAID 1.
> Thanks
> >--Original Message--
> >Your backup procedure has nothing to do with your choice
> of RAID
> >configuration (unless I'm missing something here).
> Performance and
> >Redundancy has.
> >
> >Aramid
> >
> >On Wed, 6 Apr 2005 22:50:08 -0700, "Peter"
> ><anonymous@.discussions.microsoft.com> wrote:
> >
> >>We are going to deploy a SQL Server 2000 for an
> >>application which information is static. We just
> provide
> >>queries for end users.
> >>
> >>In order to minimize the backup procedure, we would like
> >>to use RAID 1 mirroring configuration. Is this choice
> OK ?
> >
> >.
> >
>

Quicker Cursor or Table Variable

Hi
I have a large update batch to make on our database which will run overnight
when no users are logged in.
I always use a Table Variable instead of a Cursor to conserve resources, but
in this case resources are not a problem but speed is.
Which would be quicker: cursor or table variable, also would I get a
performance benefit from running the batch within a Stored Procedure rather
than Query Analyser.
Thanks
BHave you looked at the execution plan used in your batch update. This should
pinpoint where the problem is.
Use a binary approach to this. In your batch write print statements which
will display datediff statements throughout the batch. This way you will
know which portion takes the longest.
I think you will find that local table variables with indexes (primary key
constraint) offer the best performance. You will probably also find that
using one or more stored procedures offers better performance as well.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Ben" <Ben@.Newsgroups.microsoft.com> wrote in message
news:uz1niRiAGHA.916@.TK2MSFTNGP10.phx.gbl...
> Hi
> I have a large update batch to make on our database which will run
> overnight
> when no users are logged in.
> I always use a Table Variable instead of a Cursor to conserve resources,
> but
> in this case resources are not a problem but speed is.
> Which would be quicker: cursor or table variable, also would I get a
> performance benefit from running the batch within a Stored Procedure
> rather
> than Query Analyser.
> Thanks
> B
>|||Ben
I'd understan you if you ask what is a difference between a table variable
and a temporary table?
How does it relate to the cursors?
Try to avoid using cursors because it may hurt a performance , insead use
SET BASED process to update a table
If you show us what you are trying to accomplish , we van suggest something
more useful.
"Ben" <Ben@.Newsgroups.microsoft.com> wrote in message
news:uz1niRiAGHA.916@.TK2MSFTNGP10.phx.gbl...
> Hi
> I have a large update batch to make on our database which will run
> overnight
> when no users are logged in.
> I always use a Table Variable instead of a Cursor to conserve resources,
> but
> in this case resources are not a problem but speed is.
> Which would be quicker: cursor or table variable, also would I get a
> performance benefit from running the batch within a Stored Procedure
> rather
> than Query Analyser.
> Thanks
> B
>