DDL/DML triggers to monitor database security

• ranvir.panesar

  SSC Rookie

  Points: 44

  More actions

  March 31, 2009 at 7:28 am

  #384257

  Does anyone know how to use DDL or DML triggers to monitor database permissions in SQL 2005. For example when permissions are granted to a database or removed from a database.

  We had an incident at work where some permissions to a database were removed and it caused a major issue.

  Any help would be appreciated.

• Jack Corbett

  SSC Guru

  Points: 184394

  More actions

  March 31, 2009 at 8:45 am

  #969177

  You could use a DDL Trigger that watches and logs activity for several events. Instead of listing them here I'm going to point you to the BOL reference for them which is found here.

  You would want to look for security related events around logins, roles, and users.

  Jack Corbett
  Consultant - Straight Path Solutions
  Check out these links on how to get faster and more accurate answers:
  Forum Etiquette: How to post data/code on a forum to get the best help
  Need an Answer? Actually, No ... You Need a Question