Last week I posted a script that would script out all of the database permissions in a particular database in SQL Server.

After going through that exercise I couldn’t resist tackling the server level permissions too.

Here is a script that I created that will script out the server permissions from SQL Server.

If anyone has an approved script or a more streamlined approach I would love to hear about it.

declare @login nvarchar(128);
set @login = '';  -- leave blank for all logins

 select
 case a.[state]
 when 'D' then 'DENY'
 when 'R' then 'REVOKE'
 when 'G' then 'GRANT'
 when 'W' then 'GRANT'
 end + ' ' +
 case a.[type]
 when 'AAES' then 'ALTER ANY EVENT SESSION'
 when 'AAES' then 'ALTER ANY EVENT SESSION'
 when 'ADBO' then 'ADMINISTER BULK OPERATIONS'
 when 'AL' then 'ALTER'
 when 'ALAA' then 'ALTER ANY SERVER AUDIT'
 when 'ALAG' then 'ALTER ANY AVAILABILITY GROUP'
 when 'ALCD' then 'ALTER ANY CREDENTIAL'
 when 'ALCO' then 'ALTER ANY CONNECTION'
 when 'ALDB' then 'ALTER ANY DATABASE'
 when 'ALES' then 'ALTER ANY EVENT NOTIFICATION'
 when 'ALHE' then 'ALTER ANY ENDPOINT'
 when 'ALLG' then 'ALTER ANY LOGIN'
 when 'ALLS' then 'ALTER ANY LINKED SERVER'
 when 'ALRS' then 'ALTER RESOURCES'
 when 'ALSR' then 'ALTER ANY SERVER ROLE'
 when 'ALSS' then 'ALTER SERVER STATE'
 when 'ALST' then 'ALTER SETTINGS'
 when 'ALTR' then 'ALTER TRACE'
 when 'AUTH' then 'AUTHENTICATE SERVER'
 when 'CL' then CASE WHEN a.[class] = 100 THEN 'CONTROL SERVER' ELSE 'CONTROL' END -- SERVER, ENDPOINT, LOGIN
 when 'CO' then 'CONNECT'
 when 'COSQ' then 'CONNECT SQL'
 when 'CRDB' then 'CREATE ANY DATABASE'
 when 'CRDE' then 'CREATE DDL EVENT NOTIFICATION'
 when 'CRHE' then 'CREATE ENDPOINT'
 when 'CRSR' then 'CREATE SERVER ROLE'
 when 'CRTE' then 'CREATE TRACE EVENT NOTIFICATION'
 when 'IM' then 'IMPERSONATE'
 when 'SHDN' then 'SHUTDOWN'
 when 'TO' then 'TAKE OWNERSHIP'
 when 'VW' then 'VIEW DEFINITION'
 when 'VWAD' then 'VIEW ANY DEFINITION'
 when 'VWDB' then 'VIEW ANY DATABASE'
 when 'VWSS' then 'VIEW SERVER STATE'
 when 'XA' then 'EXTERNAL ACCESS ASSEMBLY'
 when 'XU' then 'UNSAFE ASSEMBLY'

 end +
 CASE a.[class]
 WHEN 100 then '' --'SERVER'
 WHEN 101 then ' on login::[' + isnull(d.name,'') + ']' -- server principal
 WHEN 105 then ' on endpoint::[' + isnull(c.name,'') + ']' --'endpoint'
 else ''
 end +
 ' to [' + rtrim(b.name) collate SQL_Latin1_General_CP1_CI_AS + ']' +
 case a.[state]
 when 'W' then ' WITH GRANT OPTION;'
 else ';'
 end
 from sys.server_permissions as [a]
 join sys.server_principals as [b] on b.principal_id = a.grantee_principal_id and (isnull(@login, '') = '' or b.[name] = @login)
 left outer join sys.endpoints as [c] on c.endpoint_id = a.major_id
 left outer join sys.server_principals as [d] on d.principal_id = a.major_id
 where b.[type] <> 'C';

</pre>
<pre>

Thanks!