Notification on User or Login being Created

• AndrewSQLDBA

  SSC-Insane

  Points: 21827

  More actions

  June 19, 2008 at 9:37 am

  #122100

  Hello Everyone

  I am working on cleaning up all the databases that I am now the DBA of. I have a mix of 2000 and 2005 databases.

  I would like to know when a user or Login gets created. I have noticed that some of the apps that use the databases are running with admin level permissions. I cannot simply go in and kank them just yet. I have to do this in small steps. How can I set up a notification that will send off an email to me when a user account is created? I would also like to know when the DDL is changed. I am looking at the DDL triggers in the BOL for that one. But any advice on that topic would be greatly appreciated.

  Thanks

  Andrew SQLDBA

• MannySingh

  SSChampion

  Points: 12465

  More actions

  June 19, 2008 at 10:22 am

  #831225

  Exactly Same Problem here.. I am this query in a job, which in return send me notifications every hour or once a day.

  select * from syslogins

  where createdate>=getdate()-1

  Or Use this for more frequent intervals (Filter columns if you have to)

  Select * from syslogins

  where createdate >= dateadd(hour,-3,getdate())

  Create linked server on one of your secured prod Boxes, to link to every SQLServer and do the same Query for all servers.

  [Which ever is convienent and Secure]

  Also Check Triggers..

  Maninder
  www.dbanation.com