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
connections.
Modufy this script for your needs

create login AuditLogin with password = ‘AuditLoginPswd’
go
/*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
IF ORIGINAL_LOGIN()= ‘AuditLogin’ AND
(SELECT COUNT(*) FROM sys.dm_exec_sessions
WHERE is_user_process = 1 AND
original_login_name = ‘AuditLogin’) > 1
ROLLBACK;

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
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
.