Differences between ALTER ANY LOGIN & securityadmin Server level role?

techresearch7777777 1,746 Reputation points
2022-07-04T19:51:01.053+00:00

Hello, what are the differences between ALTER ANY LOGIN & securityadmin Server level role?

Think I have an idea that securityadmin Server level role probably has higher permissions but would like to know more specifics.

Thanks in advance.

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,317 questions
{count} votes

Accepted answer
  1. Erland Sommarskog 99,371 Reputation points MVP
    2022-07-04T21:16:19.297+00:00

    There is an enormous difference. Securityadmin grant server-level permissions to other users. You cannot do that with ALTER ANY LOGIN only.

    The demo below illustrates. As the script is given the GRANT CONTROL SERVER command fails. Comment out GRANT ALTER ANY LOGIN and activate ALTER SERVER ROLE instead, and the GRANT fails. Yet, there is no difference in the output from fn_my_permissions.

       USE master  
       go  
       CREATE LOGIN testsecadmin WITH PASSWORD = 'TeMporrrray'  
       CREATE LOGIN otherlogin WITH PASSWORD = 'Klklklklklkl'  
       go  
       \--ALTER SERVER ROLE securityadmin ADD MEMBER testsecadmin  
       GRANT ALTER ANY LOGIN TO testsecadmin  
       go  
       EXECUTE AS LOGIN = 'testsecadmin'  
       go  
       SELECT * FROM sys.fn_my_permissions(NULL, NULL)  
       GRANT CONTROL SERVER TO otherlogin  
       go  
       REVERT  
       go  
       DROP LOGIN testsecadmin  
       DROP LOGIN otherlogin  
    
    1 person found this answer helpful.
    0 comments No comments

3 additional answers

Sort by: Most helpful
  1. techresearch7777777 1,746 Reputation points
    2022-07-05T02:30:38.863+00:00

    I had a feeling securityadmin was more higher than ALTER ANY LOGIN but didn't realize the gap was that big.

    Just pondering further if there's anything else different under the hood of differences (i.e. maybe ALTER ANY LOGIN has access to any of the system DBs = master, msdb, model, tempdb and their Stored Procedures that securityadmin does not)

    Thanks bunch Erland for your quick and informative info.


  2. Seeya Xi-MSFT 16,416 Reputation points
    2022-07-05T07:50:27.44+00:00

    Hi @techresearch7777777 ,

    Agree with Erland. Here is a related thread you can refer to: https://dba.stackexchange.com/questions/199440/why-securityadmin-does-not-have-enough-permission
    In additon, here is an article about Access Control: https://satoricyber.com/sql-server-security/sql-server-roles/
    Hope these could give you some help.

    Best regards,
    Seeya


    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    0 comments No comments

  3. techresearch7777777 1,746 Reputation points
    2022-07-06T22:20:04.943+00:00

    Thanks everyone for the helpful infos.

    0 comments No comments