locked
Provide account with permissions to create and disable/enable logins and that's all) RRS feed

  • Question

  • Hello.

    Please advise how to provide an account on SQL Server 2008 R2 with minimum security permissions (without granting DBO on master) if the account needs to have only rights to create and disable/enable other SQL logins (without ability to delete them etc.) with public role.

    Thanks in advance.

    Friday, September 6, 2013 9:17 AM

Answers

  • In order to accomplish that, you will have to create a special stored procedure, lets call it "Admin.Disable_Enable_Login"

    with EXECUTE AS SomeOtheruser

    where SomeOtheruser hast ALTER ANY LOGIN Permission

    and the procedure will have only the code that enables/disables a given login that comes in via a parameter

    in the End your Account (User) must have only permission to execute this procedure like

    GRANT EXECUTE 
    ON Admin.Disable_Enable_Login
    TO User



    Andreas Wolter | Microsoft Certified Master SQL Server
    Blog: www.insidesql.org/blogs/andreaswolter
    Web: www.andreas-wolter.com

    • Proposed as answer by Sofiya Li Monday, September 9, 2013 6:47 AM
    • Marked as answer by Sofiya Li Monday, September 16, 2013 6:24 AM
    Friday, September 6, 2013 9:47 AM

All replies

  • In order to accomplish that, you will have to create a special stored procedure, lets call it "Admin.Disable_Enable_Login"

    with EXECUTE AS SomeOtheruser

    where SomeOtheruser hast ALTER ANY LOGIN Permission

    and the procedure will have only the code that enables/disables a given login that comes in via a parameter

    in the End your Account (User) must have only permission to execute this procedure like

    GRANT EXECUTE 
    ON Admin.Disable_Enable_Login
    TO User



    Andreas Wolter | Microsoft Certified Master SQL Server
    Blog: www.insidesql.org/blogs/andreaswolter
    Web: www.andreas-wolter.com

    • Proposed as answer by Sofiya Li Monday, September 9, 2013 6:47 AM
    • Marked as answer by Sofiya Li Monday, September 16, 2013 6:24 AM
    Friday, September 6, 2013 9:47 AM
  • Great solution. You should add another sp for create login procedure or parametrize the existing sp and put some if then else logic in there.

    Regards, Dean Savović

    Tuesday, September 10, 2013 8:04 PM