locked
sql mp - Alert on Drop db_user event RRS feed

  • Question

  • Hi all,

    I have a need to monitor the deletion of database user from sql database, as in sp_dropuser. How would I go about setting this up?

    THanks in advance

    Saturday, January 5, 2013 4:22 PM

Answers

  • I'm not so sure it is a realistically scalable solution in an organisation of any size and it is potentially not reliable as if a user is dropped while another is added within an iteration of the check then that would be missed. You'd almost have to have a list of dbusers per database across the organisation which could be a major job to maintain.

    If you are on SCOM 2012 then you are also licensed for Orchestrator and I'd consider using that for monitoring of this type. We do actually use it for checking windows group memberships against an approved list - you can do this quite simply in powershell where you pull the group membership from AD, compare it the list of approved group members and then generate an alert if there is a difference. But we do it for about 5 groups ... I'd have nightmares trying to maintain that for 500 databases ;-)

    If you have the budget (and especially if this is for compliance) then perhaps take a look at Idera - http://www.idera.com/SQL-Server/sql-compliance-manager/ or Quest (now Dell) who have specific tools for SQL auditing.

    Cheers

    Graham


    Regards Graham New System Center 2012 Blog! - http://www.systemcentersolutions.co.uk
    View OpsMgr tips and tricks at http://systemcentersolutions.wordpress.com/

    • Marked as answer by Nicholas Li Tuesday, February 5, 2013 6:55 AM
    Monday, January 7, 2013 9:05 PM

All replies

  • Hi

    Ideally from a SCOM perspective, you would create a NT event based rule to alert when a particular event is detected in the windows application or security log.

    You might want to post on the SQL forums to see if it is possible to configure SQL to do this. If you can get this information then we can help you plug it into SCOM.

    Cheers

    Graham


    Regards Graham New System Center 2012 Blog! - http://www.systemcentersolutions.co.uk
    View OpsMgr tips and tricks at http://systemcentersolutions.wordpress.com/

    Saturday, January 5, 2013 11:32 PM
  • Graham,

    I don't believe there's an event ID for this, this is only exposed as trace event within sql...sounds like some custom dev work then.

    Thanks

    Sunday, January 6, 2013 4:48 PM
  •  

    Hi,

    Please see if the monitor can work with a SQL query-based monitor:

    Query a database with a monitor

    http://contoso.se/blog/?p=310

    Query a database with a monitor – part two

    http://contoso.se/blog/?p=1346

    Hope this can give you some hints.

    Thanks.  

    Nicholas Li
    TechNet Community Support

    Monday, January 7, 2013 8:09 AM
  • Hi

    There certainly isn't for earlier versions of SQL e.g. 2000 and 2005. Auditing has been improved in SQL 2008 R2 and SQL 2012 so there might be options there.

    I don't understand what Nicholas is asking you to do - perhaps he can give more details on exactley what to query with SQL?

    Cheers

    Graham


    Regards Graham New System Center 2012 Blog! - http://www.systemcentersolutions.co.uk
    View OpsMgr tips and tricks at http://systemcentersolutions.wordpress.com/

    Monday, January 7, 2013 8:22 AM
  • yes, I know exactly what he means. It's conceivable to do this, as in have one monitor count db users in a database, and raise an event if there are less than expected. Have another monitor just pick up this event.

    But that seems like an overkill work around, would have to be targeted to every database.

    Monday, January 7, 2013 7:04 PM
  • I'm not so sure it is a realistically scalable solution in an organisation of any size and it is potentially not reliable as if a user is dropped while another is added within an iteration of the check then that would be missed. You'd almost have to have a list of dbusers per database across the organisation which could be a major job to maintain.

    If you are on SCOM 2012 then you are also licensed for Orchestrator and I'd consider using that for monitoring of this type. We do actually use it for checking windows group memberships against an approved list - you can do this quite simply in powershell where you pull the group membership from AD, compare it the list of approved group members and then generate an alert if there is a difference. But we do it for about 5 groups ... I'd have nightmares trying to maintain that for 500 databases ;-)

    If you have the budget (and especially if this is for compliance) then perhaps take a look at Idera - http://www.idera.com/SQL-Server/sql-compliance-manager/ or Quest (now Dell) who have specific tools for SQL auditing.

    Cheers

    Graham


    Regards Graham New System Center 2012 Blog! - http://www.systemcentersolutions.co.uk
    View OpsMgr tips and tricks at http://systemcentersolutions.wordpress.com/

    • Marked as answer by Nicholas Li Tuesday, February 5, 2013 6:55 AM
    Monday, January 7, 2013 9:05 PM