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

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: m***@epprecht.net

MVP Program: http://www.microsoft.com/mvp

Blog: http://www.msmvps.com/epprecht/

Show quoteHide quote

Am Sat, 17 Dec 2005 19:09:57 +0100 schrieb Mike Epprecht (SQL MVP):

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

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

Show quoteHide quote

Am Sat, 17 Dec 2005 18:33:39 -0000 schrieb John Bell:

....
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

How can I create a unique constraint on 2 columns where one of them allows nulls?
Determining financial year quarter
==again== constraints with nulls
(Non) Updatable view problem
Compare feilds in seperate databases on seperate servers