login event in sql server ?

Hi,
is there a way in SQL 2000 to check a new login and to stop it?
I want to be informed if a user makes a new connect, then i want to check
the program name he uses for connect and if this is not a valid name i want
to cancel his login.
I think it should be possible with a trigger on the table where system
information about login is written, but i don't know which table.
If i have this info i could KILL unvalid connects.
thanks,
HelmutHi
In SQL Server 2000, you can not put a trigger on a system table.
You can do this though a SQL Server agent job every minute, and then code a
sp_kill to the connection that you want to kill.
BTW, the Program ID can be faked very easily as you can set it in the
connection string, so once your users/customer figure out the string you are
checking for, you could use another application to connect to the database.
You have to be very careful, as you must not kill system SPIDs, or other
ones like SQL Server agent jobs, or tools that allow you to manage the
server, as you could lock yourself out.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"helmut woess" <hw@.iis.at> wrote in message
news:5i60470r8j6x$.14q28ljnstqpf$.dlg@.40tude.net...
> Hi,
> is there a way in SQL 2000 to check a new login and to stop it?
> I want to be informed if a user makes a new connect, then i want to check
> the program name he uses for connect and if this is not a valid name i
> want
> to cancel his login.
> I think it should be possible with a trigger on the table where system
> information about login is written, but i don't know which table.
> If i have this info i could KILL unvalid connects.
> thanks,
> Helmut|||Hi
If you want to limit access like this then the most common solution would be
users table which you validate against. If you want to limit access to a
given application, then you may want to also have an application role. In
general this looks like your permissions may be too wide, or you lack
control over the logins.
John
"helmut woess" <hw@.iis.at> wrote in message
news:5i60470r8j6x$.14q28ljnstqpf$.dlg@.40tude.net...
> Hi,
> is there a way in SQL 2000 to check a new login and to stop it?
> I want to be informed if a user makes a new connect, then i want to check
> the program name he uses for connect and if this is not a valid name i
> want
> to cancel his login.
> I think it should be possible with a trigger on the table where system
> information about login is written, but i don't know which table.
> If i have this info i could KILL unvalid connects.
> thanks,
> Helmut|||Am Sat, 17 Dec 2005 19:09:57 +0100 schrieb Mike Epprecht (SQL MVP):

> Hi
> In SQL Server 2000, you can not put a trigger on a system table.
>
Oh, i didn't know that. This makes the realisation of my idea impossible,
because i want to prevent login immediately, not after 30 seconds or so. It
must look to the user as if he used a wrong password.
but thanks,
Helmut|||Am Sat, 17 Dec 2005 18:33:39 -0000 schrieb John Bell:
...
> If you want to limit access like this then the most common solution would
be
> users table which you validate against. If you want to limit access to a
> given application, then you may want to also have an application role. In
> general this looks like your permissions may be too wide, or you lack
> control over the logins.
>
It was part of a bigger project to prevent login if the user uses another
application as my program. But because i cannot use triggers on system
tables (as Mike told me) this idea has died.
thanks,
Helmut|||Hi Helmut
You can therefore use an application role to obfuscate this.
John