none
Deny drop login for a login having securtiy admin role

    Question

  • Hi Everyone , 

    We have a new security application being onboarded. Though the application is fine there are certain resitrctions we want to enforse as we do not want the application to accidentally delete any logins.

    Initially we have created a SQL Account and granted it Security Admin role at the server level and db_accessadmin at databae level. This works fine for creation of logins and users. The challenge is this permission also gives this account to drop login. We would like to restrict the drop login.

    Is there a way by which this can be achieved. ie. a user having a serurity admin role with drop login permission restricted ?

    --------------------------------
    Thanks
    Suhas Vallala
    Saturday, April 20, 2013 5:59 AM

Answers

  • Hello Suhas,

     I tired this way and it works, I do not know if there is anyway to do by Deny permissions to drop login while have securityadmin server role...

    I created a login 'Security' and gave seecurityadmin role and created this DDL trigger...

    create trigger trg_DropLogin
     on all server
    for Drop_login
    as
    IF (SUSER_NAME() = 'Security')
    begin 
    print 'cannot drop login'
    rollback
    end
    


    Hope it Helps!!

    • Marked as answer by Suhas Vallala Sunday, April 21, 2013 7:17 AM
    Saturday, April 20, 2013 7:04 AM
  • You can, however, reliably use PBM to check that your trigger is created and enabled on all servers.

    CREATE LOGIN and DROP LOGIN are both controlled by the ALTER ANY LOGIN permission.  So you can't have one without the other.  An additional option (if you own the security application) is to supply a signed or EXECUTE AS OWNER stored procedure to perform the CREATE LOGIN, and revoke the ALTER ANY LOGIN.  Although you may end up wrapping several security operations.  This would give you very granular control over the ability of this application to make only selected kinds of security changes.  If your users are not highly trusted, this is a good defense-in-depth strategy.

    David


    David http://blogs.msdn.com/b/dbrowne/

    • Marked as answer by Suhas Vallala Sunday, April 21, 2013 7:18 AM
    Saturday, April 20, 2013 10:28 PM
  • Permissions would also have to be set up on every server.

    It possible that you could use Policy-Based Management to set up a policy that disallows DROP LOGIN. You can use PBM to push policies to a lot of servers.

    However, the On Prevent option in PBM is unreliable, as PBM may decide that the condition for On Prevent is too complex and silently turn of the DDL trigger it creates.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marked as answer by Suhas Vallala Sunday, April 21, 2013 7:18 AM
    Saturday, April 20, 2013 7:22 PM

All replies

  • Hello Suhas,

     I tired this way and it works, I do not know if there is anyway to do by Deny permissions to drop login while have securityadmin server role...

    I created a login 'Security' and gave seecurityadmin role and created this DDL trigger...

    create trigger trg_DropLogin
     on all server
    for Drop_login
    as
    IF (SUSER_NAME() = 'Security')
    begin 
    print 'cannot drop login'
    rollback
    end
    


    Hope it Helps!!

    • Marked as answer by Suhas Vallala Sunday, April 21, 2013 7:17 AM
    Saturday, April 20, 2013 7:04 AM
  • Thank you Stanley for the response ..

    My mistake, i should have mentioned that i am aware of creating DDL triger .. This workaround will not work in my environment :( ...

    Is there any other way say server level permissions  i can restirict it ? i looked after deny any login but this dosent allow creating login either !!!

    -----------------------

    Thanks,Suhas Vallala

    Saturday, April 20, 2013 5:17 PM
  • DROP LOGIN requires ALTER ANY LOGIN. Thus you can deny the account ALTER ANY LOGIN. But this may lead to that there are legit operations that fails.

    Why will the DDL trigger not work for you?


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Saturday, April 20, 2013 6:30 PM
  • I need to implement DDL trigger across all the servers ! i find this method good if it is implemented for only one server but not across the environment level !
    If there is no other option then i need take DDL trigger as the only workaround ... 

    I was just curious if there is any other alternate way !!                                                                       

    ----------------------------------------

    Thanks,Suhas Vallala

    Saturday, April 20, 2013 6:56 PM
  • Permissions would also have to be set up on every server.

    It possible that you could use Policy-Based Management to set up a policy that disallows DROP LOGIN. You can use PBM to push policies to a lot of servers.

    However, the On Prevent option in PBM is unreliable, as PBM may decide that the condition for On Prevent is too complex and silently turn of the DDL trigger it creates.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marked as answer by Suhas Vallala Sunday, April 21, 2013 7:18 AM
    Saturday, April 20, 2013 7:22 PM
  • You can, however, reliably use PBM to check that your trigger is created and enabled on all servers.

    CREATE LOGIN and DROP LOGIN are both controlled by the ALTER ANY LOGIN permission.  So you can't have one without the other.  An additional option (if you own the security application) is to supply a signed or EXECUTE AS OWNER stored procedure to perform the CREATE LOGIN, and revoke the ALTER ANY LOGIN.  Although you may end up wrapping several security operations.  This would give you very granular control over the ability of this application to make only selected kinds of security changes.  If your users are not highly trusted, this is a good defense-in-depth strategy.

    David


    David http://blogs.msdn.com/b/dbrowne/

    • Marked as answer by Suhas Vallala Sunday, April 21, 2013 7:18 AM
    Saturday, April 20, 2013 10:28 PM
  • Thank You All for the inputs ..

    --------------------

    Thanks,Suhas Vallala

    Sunday, April 21, 2013 7:17 AM