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 ?

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

Logon auditing through trigger on sysprocesses?

I need to do auditing of logons, and selectively (depending on who does
them) of updates to a single table as well.
Furthermore, to make remote access to the audit log easier, I would prefer
to log all this in a table on the same server.
Full c2 auditing would give me far too much information (and I haven't even
checked if it is supported in MSDE). It would probably reduce performance
as well.
Full auditing of all updates to just that one table where I need it, would
give too much information too: one certain application that's running on
the local machine should be left out.
Now I think I could get everything I want done through triggers, but
there's one problem.
The 'sysprocesses' table seems like a good place to detect logons, if I
could create an INSERT trigger that copies username and hostname to a
logging table for each new record.
The problem: the db engine won't let me create a trigger on the
sysprocesses table, it keeps saying "access denied" no matter how I try to
override and change permissions.
I realize that there could be some danger too, if executing a trigger would
create a new process. But will it? I would expect that a trigger is
executed in the context of the process that initiated the change.
hi,
Lucvdv wrote:
>...
> The 'sysprocesses' table seems like a good place to detect logons, if
> I could create an INSERT trigger that copies username and hostname to
> a logging table for each new record.
> The problem: the db engine won't let me create a trigger on the
> sysprocesses table, it keeps saying "access denied" no matter how I
> try to override and change permissions.
>
sysprocesses is a "fake" table, in the sense it is materialized on demand,
and you can't perform INSERT, UPDATE, or DELETE operations on this kind of
table no matter how much privilege you have on the system, and of course you
can not write trigger on it..
as regard auditing, try perhaps having a look at
http://www.windowsitpro.com/Article/...434/26434.html ...
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.15.0 - DbaMgr ver 0.60.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Andrea Montanari wrote:
> hi,
> Lucvdv wrote:
> sysprocesses is a "fake" table, in the sense it is materialized on demand,
> and you can't perform INSERT, UPDATE, or DELETE operations on this kind of
> table no matter how much privilege you have on the system, and of course you
> can not write trigger on it..
> as regard auditing, try perhaps having a look at
> http://www.windowsitpro.com/Article/...434/26434.html ...
Thanks.
It wasn't of much help though
The article is about finding out whodunnit in a situation where
everyone has the sa password, and suggests giving each user his own
database to connect to.
In my case each user has his own logon credentials, all changes except
those made by a certain account should be logged, and changes made by
any user should be visible to all.
That part shouldn't be too hard to do, the difficulty is knowing when
someone logs on and from where.
|||On Tue, 30 Aug 2005 19:31:30 +0200, Lucvdv <name@.null.net> wrote:

> In my case each user has his own logon credentials, all changes except
> those made by a certain account should be logged, and changes made by
> any user should be visible to all.
> That part shouldn't be too hard to do, the difficulty is knowing when
> someone logs on and from where.
The change logging part is working, with a simple trigger.
There is a performance hit of course, but it doesn't seem to be too much
(I've tested it in a real live database for about an hour, and didn't see
any noticeable system slowdown or abnormally high CPU use).
In case someone wants to use it as a model, this is the 'simple' version
with a test table I started from:
The 'Test' table just has 2 columns Col1 and Col2, both integer, with Col1
as primary key.
The 'TestLog' table must exist before the trigger is added, with columns
(in this case) DT DateTime, spid smallint, Col1 int, oldCol2 int, newCol2
int, user varchar(128), host varchar(128), prog varchar(128).
In the real test I included an identity column, so sequence numbers will be
missing if someone deletes log lines.
CREATE TRIGGER [trgTest] ON dbo.Test
FOR UPDATE
AS
DECLARE @.user varchar(128), @.host varchar(128), @.prog varchar(128)
BEGIN
SELECT @.user = RTRIM([nt_username]), @.host = RTRIM([hostname]),
@.prog = RTRIM([program_name])
FROM [master].[dbo].[sysprocesses] WHERE spid=@.@.SPID
IF @.prog<>'My application'
INSERT INTO [TestLog]
SELECT GetDate(), @.@.SPID, [Inserted].[Col1], [Deleted].[Col2],
[Inserted].[Col2], @.user, @.host, @.prog
FROM [Inserted] INNER JOIN [Deleted]
ON [Inserted].[Col1]=[Deleted].[Col1]
WHERE [Inserted].[Col2]<>[Deleted].[Col2]
END
|||have a look at
http://www.sqlservercentral.com/colu...qlprofiler.asp
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.15.0 - DbaMgr ver 0.60.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply

Login Triggers?

Quick question about SQL 2000. Are there ways to have a trigger fire when
someone (anyone) logs in to a MSSQL SQL 2000 instance? I had thought MSSQL
came with login triggers but I haven't been able to find any info.
TIA,
JDNo, sorry.
If you want to track logins, you can log all login attempts. Or you can run
a SQL Trace that captures that information. However, if you wanted to do
some additional work for the user, there is no trigger for that.
RLF
"Joe D" <jkdriscoll@.qg.com> wrote in message
news:dvph8d$2k91$1@.sxnews1.qg.com...
> Quick question about SQL 2000. Are there ways to have a trigger fire when
> someone (anyone) logs in to a MSSQL SQL 2000 instance? I had thought MSSQL
> came with login triggers but I haven't been able to find any info.
> TIA,
> JD
>|||thanks for the feed back Russell.
Do you if SQL 2005 will offer it?
Thanks again.
"Russell Fields" <RussellFields@.NoMailPlease.Com> wrote in message
news:O5cm9uRTGHA.4900@.TK2MSFTNGP12.phx.gbl...
> No, sorry.
> If you want to track logins, you can log all login attempts. Or you can
> run a SQL Trace that captures that information. However, if you wanted to
> do some additional work for the user, there is no trigger for that.
> RLF
> "Joe D" <jkdriscoll@.qg.com> wrote in message
> news:dvph8d$2k91$1@.sxnews1.qg.com...
>|||Hello Joe,

> Do you if SQL 2005 will offer it?
Thats an interesting question. The answer is yes, but you'll have to be a
bit more creative than you might like.
In 2005 you can do an CREATE EVENT NOTIFICATION on SERVER FOR AUDIT_LOGIN|AU
DIT_LOGIN_FAILED|AUDIT_LOGOUT
etc and send that payload to a service broker queue for processing. The spro
c
that processes queued messages could then do what you neeed more than likely
.
Thank you,
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/|||Interesting.
Thanks Kent.
"Kent Tegels" <ktegels@.develop.com> wrote in message
news:b87ad741c12a8c81b0a6c56b8f0@.news.microsoft.com...
> Hello Joe,
>
> Thats an interesting question. The answer is yes, but you'll have to be a
> bit more creative than you might like.
> In 2005 you can do an CREATE EVENT NOTIFICATION on SERVER FOR
> AUDIT_LOGIN|AUDIT_LOGIN_FAILED|AUDIT_LOG
OUT etc and send that payload to a
> service broker queue for processing. The sproc that processes queued
> messages could then do what you neeed more than likely.
> Thank you,
> Kent Tegels
> DevelopMentor
> http://staff.develop.com/ktegels/
>|||>> Thats an interesting question. The answer is yes, but you'll have to[vbcol=seagreen]
Closing the loop on this: http://www.sqljunkies.com/WebLog/kt...ifications.aspx
Enjoy!
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/

Login Trigger?

Hi,
I'm looking for something similar to Oracle's login trigger to limit user
connections. I have an application userid that must have read/write/execute
permissions, but other users who should only have read access are logging in
with this user id. This user id is used by a legacy system and no one how
many places the password is hard coded, so changing the password is not an
option.
Is there a way that I can check the hostname of this user on connection, and
if the connection is not coming from an approved host, deny the connection?
Thanks.
SusanI only know a windows trick MIGHT achieve the effect. You can test this: try
setup the sql server's security policy to only allow certain machines to
login. But if a user login from terminal services or citrix client, you need
to think other methods.
James
"Susan Cooper" wrote:
> Hi,
> I'm looking for something similar to Oracle's login trigger to limit user
> connections. I have an application userid that must have read/write/execute
> permissions, but other users who should only have read access are logging in
> with this user id. This user id is used by a legacy system and no one how
> many places the password is hard coded, so changing the password is not an
> option.
> Is there a way that I can check the hostname of this user on connection, and
> if the connection is not coming from an approved host, deny the connection?
> Thanks.
> Susan

Login Trigger?

Hi,
I'm looking for something similar to Oracle's login trigger to limit user
connections. I have an application userid that must have read/write/execute
permissions, but other users who should only have read access are logging in
with this user id. This user id is used by a legacy system and no one how
many places the password is hard coded, so changing the password is not an
option.
Is there a way that I can check the hostname of this user on connection, and
if the connection is not coming from an approved host, deny the connection?
Thanks.
Susan
I only know a windows trick MIGHT achieve the effect. You can test this: try
setup the sql server's security policy to only allow certain machines to
login. But if a user login from terminal services or citrix client, you need
to think other methods.
James
"Susan Cooper" wrote:

> Hi,
> I'm looking for something similar to Oracle's login trigger to limit user
> connections. I have an application userid that must have read/write/execute
> permissions, but other users who should only have read access are logging in
> with this user id. This user id is used by a legacy system and no one how
> many places the password is hard coded, so changing the password is not an
> option.
> Is there a way that I can check the hostname of this user on connection, and
> if the connection is not coming from an approved host, deny the connection?
> Thanks.
> Susan

Login Trigger?

Hi,
I'm looking for something similar to Oracle's login trigger to limit user
connections. I have an application userid that must have read/write/execute
permissions, but other users who should only have read access are logging in
with this user id. This user id is used by a legacy system and no one how
many places the password is hard coded, so changing the password is not an
option.
Is there a way that I can check the hostname of this user on connection, and
if the connection is not coming from an approved host, deny the connection?
Thanks.
SusanI only know a windows trick MIGHT achieve the effect. You can test this: try
setup the sql server's security policy to only allow certain machines to
login. But if a user login from terminal services or citrix client, you need
to think other methods.
James
"Susan Cooper" wrote:

> Hi,
> I'm looking for something similar to Oracle's login trigger to limit user
> connections. I have an application userid that must have read/write/execu
te
> permissions, but other users who should only have read access are logging
in
> with this user id. This user id is used by a legacy system and no one how
> many places the password is hard coded, so changing the password is not an
> option.
> Is there a way that I can check the hostname of this user on connection, a
nd
> if the connection is not coming from an approved host, deny the connection
?
> Thanks.
> Susan