Easy points.......maybe: Trigger to prevent logon to a DB

• Warren Peace

  SSCertifiable

  Points: 5871

  More actions

  March 19, 2014 at 3:39 pm

  #291080

  OK, I know about this: http://qa.sqlservercentral.com/scripts/Security/69558/

  But the script has "ALL SERVER".

  What I want is a trigger that is specific to my DB called "JunkStuff". I only want to block a servername from connect to my super dooper DB "JunkStuff".

  😀

  Thanks

• Warren Peace

  SSCertifiable

  Points: 5871

  More actions

  March 19, 2014 at 4:36 pm

  #1698544

  Hmmm...

  Will this work????

  CREATE TRIGGER [connection_trigger]

  ON ALL SERVER WITH EXECUTE AS '...'

  FOR LOGON

  AS

  BEGIN

  IF EXISTS = (SELECT ORIGINAL_LOGIN_NAME FROM sys.dm_exec_sessions WHERE database_id = 11 AND ORIGINAL_LOGIN_NAME = 'mydomain\prefix-MV-purpose$')

  ROLLBACK;

  END;

  GO

  ENABLE TRIGGER [connection_trigger] ON ALL SERVER

  GO

  Haven't tried it yet. I think it's a winner though....lol:hehe: