Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Using Logon Trigger to exclude SSMS connections Expand / Collapse
Author
Message
Posted Monday, October 28, 2013 2:01 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, September 16, 2014 7:35 PM
Points: 37, Visits: 136
Hi all,
Somehow, the application ID/PW became known to certain individuals within my organization. Someone ran a rogue query against the database via SSMS which brought down production! We cannot change the Application ID/PW because of the magnitude of such an effort.
I thought a logon trigger would take care of someone using the application ID to query the database.
I created the following
CREATE TRIGGER [Prevent_ApplicationID]
ON ALL SERVER
FOR LOGON
AS
BEGIN

IF ORIGINAL_LOGIN() = 'test' AND
(select count(*) FROM sys.sysprocesses
where spid = @@SPID
and (program_name like 'Microsoft SQL Server Management Studio%' or
program_name = 'SQL Query Analyzer'
or program_name = 'Toad for Data Analysts'
)) > 0
BEGIN
ROLLBACK;
END
END;

But it isn't working as expected. I can still access via SSMS and TOAD.
Any idea what is wrong with my trigger?

Thanks!
Post #1509106
Posted Monday, October 28, 2013 2:18 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, September 16, 2014 7:35 PM
Points: 37, Visits: 136
Nevermind. I apologize for wasting people's time. If Mods see this, please delete.

I didn't search the forum thoroughly enough. Found my answer
Post #1509112
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse