I need to get a list of MS SQL Server fixed roles and their permissions.
There is a query to get all server-level permissions:
SELECT * FROM sys.fn_builtin_permissions('SERVER') ORDER BY permission_name;
And there is a SP sys.sp_srvrolepermission
which returns list of server roles and their 'permissions'. But those permissions don't really map to what the previous query returns.
There is a pretty good explanation here https://docs.microsoft.com/en-us/sql/relational-databases/security/authentication-access/server-level-roles?view=sql-server-ver15 with image, which shows roles and permissions. And permissions on the image map to the first query results.
The question is - is there a correct way (SP, query) to get server fixed roles to permissions mapping, or should I hardcode those as per image.
sys.sp_srvrolepermission
is deprecated and marked as to be removed from SQL Server, per the documentation. This, however, looks like a question for Database Administrators, not Stack Overflow.