Trigger : Reject all login from mixed authentication except 'test' login from one server name 'Server1'

/*


Created by : Aditya Jha

Created On : 22/06/20012

Purpose : Reject all login from mixed authentication except 'test' login from one server name 'Server1'

*/

Create TRIGGER tr_reject_mixauth_logon ON ALL SERVER

FOR LOGON

AS

BEGIN

DECLARE @ClientHost nvarchar(max);

SELECT @ClientHost = EVENTDATA().value('(/EVENT_INSTANCE/ClientHost)[1]','nvarchar(max)');

IF (host_name() !='SUNMOSSSQL01' and suser_name() in ('test')) or
suser_name() in (select loginname from sys.syslogins where password is not null and loginname <> 'test')
ROLLBACK;
END;

--DROP TRIGGER tr_reject_mixauth_logon ON ALL SERVER;