Jasinski Technical Wiki

Navigation

Home Page
Index
All Pages

Quick Search
»
Advanced Search »

Contributor Links

Create a new Page
Administration
File Management
Login/Logout
Your Profile

Other Wiki Sections

Software

PoweredBy

Database Trigger Sample - SQL Server

RSS
Modified on Tue, Dec 03, 2013, 9:15 AM by Administrator Categorized as SQL Server
The following database trigger will allow the MyUser user to login only from the MyWebServer machine.

if exists (select 2 from sys.server_triggers where name = N'logon_test_trigger')
    drop trigger logon_test_trigger on all server
go
CREATE TRIGGER logon_test_trigger
ON ALL SERVER WITH EXECUTE AS 'sa' FOR LOGON AS
BEGIN

    /* Within a logon trigger, ROLLBACK will cancel/disable the logon */

    IF ORIGINAL_LOGIN() = 'MyUser' 
    AND UPPER(HOST_NAME()) <> 'MYWEBSERVER'

        ROLLBACK; 

END

ScrewTurn Wiki version 3.0.1.400. Some of the icons created by FamFamFam. Except where noted, all contents Copyright © 1999-2024, Patrick Jasinski.