Check Who has Server Level Access, SQL Server

Using below two SQL queries we can retrieve who has what access on the database server.
I have divided it into two-parts

1. Role access [ sp_addsrvrolemember & sp_dropsrvrolemember ]
2. Grant/Deny access [ GRANT .. & DENY .. ]

1. Role access: this SQL will show the SQL command of Add/Remove user to this role. So that you can use any to add/ or remove

select prm.name as "UserName", prr.name as "ServerRole", 'exec sp_addsrvrolemember '''+prm.name+''','''+prr.name+'''' as GrantCMD, 'exec sp_dropsrvrolemember '''+prm.name+''','''+prr.name+'''' as DenyCMD from sys.server_role_members srm left join sys.server_principals AS prm on prm.principal_id = srm.member_principal_id left join sys.server_principals AS prr on prr.principal_id = srm.role_principal_id

The output of this SQL would be like below

Role Access

2. Grant/Deny access: using below SQL, we can retrieve the user list with the access name. I have also prepared the GRANT/DENY command to use when required.

select pr.name as "UserName", sp.permission_name, sp.state_desc, sp.state_desc collate SQL_Latin1_General_CP1_CI_AS+' ' +sp.permission_name collate SQL_Latin1_General_CP1_CI_AS+' TO ' +QUOTENAME( pr.name collate SQL_Latin1_General_CP1_CI_AS) as Command from sys.server_permissions sp left join sys.server_principals AS pr on pr.principal_id = sp.grantee_principal_id where pr.name not in ('public','sa','NT AUTHORITY\SYSTEM') and pr.name not like '##%##' and sp.permission_name not in ('CONNECT SQL')

The output of this SQL would be like below:

Grant / Deny Access

Thanks.