Additional SQL Server features and topics not covered by specific categories
Thanks everyone for the helpful infos.
This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
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.
Additional SQL Server features and topics not covered by specific categories
Thanks everyone for the helpful infos.
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.
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.
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