Post reply

trigger to restrict access for changing login permissions in sql server 2008

  • August 27, 2013 at 9:51 am

    #276610

    Hi Friends,

    Let us assume I have 2 logins XXX & YYY.

    XXX-->sysadmin

    YYY-->all databases reader permission.

    XXX is trying to change the permission for YYY login to sysadmin or db_owner for all databases. Is there a way to restrict the access instead of removing the sysadmin privilege for XXX login.

    Thanks in advance..

  • August 28, 2013 at 1:25 am

    #1645419

    Hi Grasshopper,

    you can make new database role, depending on your specifications. Set your XXX user to that role

  • August 28, 2013 at 1:30 am

    #1645423

    So you don't want XXX to be able to change YYY's permissions? Start by removing XXX from sysadmin. What do you want XXX to be able to do?

    John

  • August 28, 2013 at 1:39 am

    #1645424

    There is nothing you can do to stop a sysadmin from doing whatever he wants. If you add a trigger to prevent him from changing a login, he can drop or disable the trigger, make the change and re-enable the trigger.

    If you have someone who shouldn't have sysadmin-level access,then they shouldn't have the sysadmin role.

    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

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

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