Re: Authentication options with SS2005

Uri Dimant (urid@xxxxxxxxxxx) writes:
SP2 should be installed on the system
This trigger prevents from 'AuditLogin' login having more that one
Modufy this script for your needs

create login AuditLogin with password = ‘AuditLoginPswd’
/*Create a very simple login trigger */
create trigger AuditLogin_Demo
/* server means instance level*/
on all server
with execute as self
/* We specify the logon event at this stage
– If there are more than one connections,
– Issue a rollback*/
for logon
as begin
(SELECT COUNT(*) FROM sys.dm_exec_sessions
WHERE is_user_process = 1 AND
original_login_name = ‘AuditLogin’) > 1

Keep in mind that many applications make more than one simultaneous
connection, so the above trigger can pull the rug for them.

Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx

Books Online for SQL Server 2005 at
Books Online for SQL Server 2000 at