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
MoeNo, 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...
> > 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 ?
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 ?|||Thanks. That would work for that reqt. Another reqt is this
If a user connects to the DB say Via MS-Access, I don't want them to access
the DB via MS-access, so kill that process. Same user if he accesses the DB
via SQL Query analyzer, that's ok, allow it. If I set up a job to run every
min, the user could be in the DB before the check happens, damage is already
done. Any ideas on that ?
TIA
Mo
"Aaron Bertrand [SQL Server MVP]" wrote:
> > 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
>|||> Thanks. That would work for that reqt. Another reqt is this
> If a user connects to the DB say Via MS-Access, I don't want them to
> access
> the DB via MS-access, so kill that process. Same user if he accesses the
> DB
> via SQL Query analyzer, that's ok, allow it.
You can check application name by inserting the results of sp_who2 into a
#table, but bear in mind that this value can be spoofed.
> If I set up a job to run every
> min, the user could be in the DB before the check happens, damage is
> already
> done.
You can have it check every second if you want... or at least as many times
per minute as it is possible, given that it will likely take more than a
second to check.
> Any ideas on that ?
Upgrade to SQL Server 2005? Uninstall Access from this guy's machine? Hire
developers you can trust?|||That would work. Sometimes I have seen sp_who2 take a while. Isn't that a
concern ? If it is would I be better of just selecting the columns I want
from sysprocesses into # table ?
TIA
Moe
"Aaron Bertrand [SQL Server MVP]" wrote:
> > Thanks. That would work for that reqt. Another reqt is this
> > If a user connects to the DB say Via MS-Access, I don't want them to
> > access
> > the DB via MS-access, so kill that process. Same user if he accesses the
> > DB
> > via SQL Query analyzer, that's ok, allow it.
> You can check application name by inserting the results of sp_who2 into a
> #table, but bear in mind that this value can be spoofed.
> > If I set up a job to run every
> > min, the user could be in the DB before the check happens, damage is
> > already
> > done.
> You can have it check every second if you want... or at least as many times
> per minute as it is possible, given that it will likely take more than a
> second to check.
> > Any ideas on that ?
> Upgrade to SQL Server 2005? Uninstall Access from this guy's machine? Hire
> developers you can trust?
>|||> That would work. Sometimes I have seen sp_who2 take a while. Isn't that a
> concern ? If it is would I be better of just selecting the columns I want
> from sysprocesses into # table ?
Well, there is no program_name column in sysprocesses, for starters.
In SQL Server 2005, it seems to get this information from an internal view
(sys.sysprocesses_ex) which is off limits even to call, never mind view its
definition...
At this moment I don't have a 2000 system handy where I can check the
definition of sp_who2 on that edition, but you are more than welcome to see
where 2000 got its data for program name by viewing the definition of that
procedure in the master database...
But anyway, what is "a while"? How often is "sometimes"? What is the
likelihood that all three things will happen... a user will log on using
their 11th connection with MS Access, this slow symptom will appear, and
that will be the time they happen to decide to do "damage" and they will do
it before you are made aware that they used their 11th connection? While
more likely than winning the lottery, I don't think this will be a common
scenario at all.
If something is causing sp_who2 to return rows slowly, it will probably
affect a select from sysprocesses as well (either because it is specific to
sysprocesses or because it is environment-wide).
A