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 |
