Logon trigger in sql 2000 ?

Hi,
We have a reqt to do certain things as soon as a user logins into our
server. Is there a logon trigger in sql 2k ? For ex. We need to check things
like how many connections he already has before we allow another connection.
TIA
Moe
No, login triggers was introduced in 2005 sp2. Prior to that you could either do such checks on the
connecting session or use some polling mechanism. Neither very elegant...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"MO" <MO@.discussions.microsoft.com> wrote in message
news:E7CC7761-64F0-46D6-99FF-39B8B3287CE8@.microsoft.com...
> Hi,
> We have a reqt to do certain things as soon as a user logins into our
> server. Is there a logon trigger in sql 2k ? For ex. We need to check things
> like how many connections he already has before we allow another connection.
> TIA
> Moe
|||TQ Tibor. I am not sure what you mean by polling mechanism. Could you give me
more details on that ?
TIA
Moe
"Tibor Karaszi" wrote:

> No, login triggers was introduced in 2005 sp2. Prior to that you could either do such checks on the
> connecting session or use some polling mechanism. Neither very elegant...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "MO" <MO@.discussions.microsoft.com> wrote in message
> news:E7CC7761-64F0-46D6-99FF-39B8B3287CE8@.microsoft.com...
>
|||> TQ Tibor. I am not sure what you mean by polling mechanism. Could you give
> me
> more details on that ?
Well, let's say you don't want more than 10 connections per user, ok? Then
you can create a SQL Server Agent job that wakes up every minute, and does
this:
SELECT
SUSER_SNAME([sid]),
COUNT(DISTINCT spid)
FROM master..sysprocesses
GROUP BY SUSER_SNAME([sid])
HAVING COUNT(*) > 10
ORDER BY 2 DESC;
If rows are found, then take action! I'm not sure what that means in your
case, do you want an e-mail every time this happens so you will know about
it immediately, do you want to try to kill the oldest spid from each login,
do you want to log the information to a table, etc. etc. etc. If you are
using a specific database and there are one or more procedures that are hit
frequently, you could log the information to a table (deleting them every
time, of course), check if the current user is in the "too many connections"
list, and immediately return an error instead of performing the work.
A
|||Whoops, you'd probably also want to filter on spid > 50
"MO" <MO@.discussions.microsoft.com> wrote in message
news:DBF47D85-244F-423C-B873-816CF1DB725E@.microsoft.com...
> TQ Tibor. I am not sure what you mean by polling mechanism. Could you give
> me
> more details on that ?