Prevent grants to public database role

  • speja01

    SSC Eights!

    Points: 984

    Hi All,

    I'm trying to prevent anyone from being able to apply grants to just the public database role. There doesn't appear to be an event type to help me prevent this.....Any ideas?

  • Adi Cohn-120898

    SSC-Dedicated

    Points: 33944

    I didn’t try it, so I can’t tell you for sure that it works, but if I had to come up with a solution for it, I would have try to write a trigger in the database for grant, deny and revoke events. In the trigger I’d check if the permissions were modified for the public role. If it did, I would rollback the operation. As I wrote at the beginning, I didn’t check it, but this would be the first direction that I would explore.

    Adi

    --------------------------------------------------------------
    To know how to ask questions and increase the chances of getting asnwers:
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • Silverfox

    SSC-Insane

    Points: 23090

    you can deny/remove rights to/from the public role, we have had to do that before as part of auditing and compliance.

    --------------------------------------------------------------------------------------
    [highlight]Recommended Articles on How to help us help you and[/highlight]
    [highlight]solve commonly asked questions[/highlight]

    Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
    Managing Transaction Logs by Gail Shaw[/url]
    How to post Performance problems by Gail Shaw[/url]
    Help, my database is corrupt. Now what? by Gail Shaw[/url]

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

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