Post reply

Possible to write a trigger to monitor for specific privileges being granted?

  • April 8, 2014 at 5:10 am

    #284286

    We're working with a customer right now who's looking to move their existing SQL server to our control. Currently, one of their staff has SA rights on the SQL, which is a no-no in our environment. One of the items this user performs so as to not pester me (it's a weird situation) is controlling which of their users have access to which DB, and unlocking accounts (which should be a very, very infrequent thing anyways, they're all AD logins)

    Anyways. What I was thinking might work, so that this user can continue to do that, while removing them from SA, is to make them part of the securityadmins role. BUT, in doing further research on this role, it turns out that someone in this role can explicitly create an account and grant that account Control Server privilege...

    Oops.

    What I'm looking at doing, if possible, would be some sort of either an Agent Alert, or a Trigger, which would fire to let *me* know an account has been granted this privilege. It wouldn't block the action, only warn.

    Now, this is more a paranoia thing on my part, as everyone who works here has been vetted, and I do trust the user in question to not abuse their privileges (they haven't so far with SA,) but there's always a first time...

    I suppose I could put an Agent job together that would run once or twice a day monitoring the users with Control Server, and send an e-mail if it changes, but I'm looking for something a bit more "immediate." Mostly because if someone gets granted this privilege, it's probably not going to be for a good reason...

    Thanks,

    Jason

  • April 8, 2014 at 6:19 am

    #1704106

    Try a DDL trigger on the DDL_SERVER_SECURITY_EVENTS event category.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • April 8, 2014 at 6:36 am

    #1704111

    Thanks Gail, looks like that's going to be what I'm looking for!

  • April 8, 2014 at 6:42 am

    #1704113

    And lo and behold, I do a little more googling on creating such a trigger (and again, thanks to Gail for putting me on the right track) and I find this article:

    http://www.sqlservercentral.com/articles/Security/70905/[/url]

    I *love* this site!

    :hehe:

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply