如何編寫關於登錄權限的授權視圖？

我在沒有任何權限的情況下創建了以下登錄名。

USE [master]
GO

   IF NOT EXISTS (SELECT * FROM sys.server_principals WHERE name = N'Radhe')
   CREATE LOGIN [Radhe] WITH PASSWORD=N'HareKrishna108, 
   DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
   GO

當我以特定登錄名執行以下查詢時，我得到以下結果：

execute as login='Radhe'

select * from sys.syslogins

現在我授予一些權限，[Radhe]以便她可以查看我在同一台伺服器上的一些現有登錄：

GRANT VIEW DEFINITION ON LOGIN::[site_helpdesk] TO [Radhe]
GRANT VIEW DEFINITION ON LOGIN::[camper] TO [Radhe]
GRANT VIEW DEFINITION ON LOGIN::[productuser] TO [Radhe]

現在，當我執行以下程式碼時：

execute as login='Radhe'

select * from sys.syslogins

我可以看到我已授予[Radhe]相關權限的登錄名：

我需要創建這個相同的登錄名並在多台伺服器上授予這些相同的權限。

如何編寫我在上面授予的這些權限？

我在這個不錯的網站上找到了以下腳本，但它似乎不適用於特別是上面列出的權限：

--https://www.datavail.com/blog/scripting-out-the-logins-server-role-assignments-and-server-permissions/
/********************************************************************************************************************/
-- Scripting Out the Logins, Server Role Assignments, and Server Permissions
/********************************************************************************************************************/
SET NOCOUNT ON
-- Scripting Out the Logins To Be Created
SELECT 'IF (SUSER_ID('+QUOTENAME(SP.name,'''')+') IS NULL) BEGIN CREATE LOGIN ' +QUOTENAME(SP.name)+
              CASE 
                   WHEN SP.type_desc = 'SQL_LOGIN' THEN ' WITH PASSWORD = ' +CONVERT(NVARCHAR(MAX),SL.password_hash,1)+ ' HASHED, CHECK_EXPIRATION = ' 
                       + CASE WHEN SL.is_expiration_checked = 1 THEN 'ON' ELSE 'OFF' END +', CHECK_POLICY = ' +CASE WHEN SL.is_policy_checked = 1 THEN 'ON,' ELSE 'OFF,' END
                   ELSE ' FROM WINDOWS WITH'
               END 
      +' DEFAULT_DATABASE=[' +SP.default_database_name+ '], DEFAULT_LANGUAGE=[' +SP.default_language_name+ '] END;' COLLATE SQL_Latin1_General_CP1_CI_AS AS [-- Logins To Be Created --]
FROM sys.server_principals AS SP LEFT JOIN sys.sql_logins AS SL
       ON SP.principal_id = SL.principal_id
WHERE SP.type IN ('S','G','U')
       AND SP.name NOT LIKE '##%##'
       AND SP.name NOT LIKE 'NT AUTHORITY%'
       AND SP.name NOT LIKE 'NT SERVICE%'
       AND SP.name <> ('sa');

-- Scripting Out the Role Membership to Be Added
SELECT 
'EXEC master..sp_addsrvrolemember @loginame = N''' + SL.name + ''', @rolename = N''' + SR.name + '''
' AS [-- Server Roles the Logins Need to be Added --]
FROM master.sys.server_role_members SRM
   JOIN master.sys.server_principals SR ON SR.principal_id = SRM.role_principal_id
   JOIN master.sys.server_principals SL ON SL.principal_id = SRM.member_principal_id
WHERE SL.type IN ('S','G','U')
       AND SL.name NOT LIKE '##%##'
       AND SL.name NOT LIKE 'NT AUTHORITY%'
       AND SL.name NOT LIKE 'NT SERVICE%'
       AND SL.name <> ('sa');


-- Scripting out the Permissions to Be Granted
SELECT 
   CASE WHEN SrvPerm.state_desc <> 'GRANT_WITH_GRANT_OPTION' 
       THEN SrvPerm.state_desc 
       ELSE 'GRANT' 
   END
   + ' ' + SrvPerm.permission_name + ' TO [' + SP.name + ']' + 
   CASE WHEN SrvPerm.state_desc <> 'GRANT_WITH_GRANT_OPTION' 
       THEN '' 
       ELSE ' WITH GRANT OPTION' 
   END collate database_default AS [-- Server Level Permissions to Be Granted --] 
FROM sys.server_permissions AS SrvPerm 
   JOIN sys.server_principals AS SP ON SrvPerm.grantee_principal_id = SP.principal_id 
WHERE   SP.type IN ( 'S', 'U', 'G' ) 
       AND SP.name NOT LIKE '##%##'
       AND SP.name NOT LIKE 'NT AUTHORITY%'
       AND SP.name NOT LIKE 'NT SERVICE%'
       AND SP.name <> ('sa');

SET NOCOUNT OFF

授予登錄權限的伺服器權限的 class_desc 為SERVER_PRINCIPAL. 因此，在這種情況下，您可以包含這些ON LOGIN::位並（再次）加入反對sys.server_principals. 還告訴 radhe 和其他人請使用該視圖而不是sys.syslogins13 年前已棄用的視圖…

SELECT 
   CASE WHEN SrvPerm.state_desc <> 'GRANT_WITH_GRANT_OPTION' 
       THEN SrvPerm.state_desc 
       ELSE 'GRANT' 
   END
   + ' ' + SrvPerm.permission_name 
   + CASE SrvPerm.class_desc WHEN 'SERVER_PRINCIPAL' THEN 
     ' ON LOGIN::' + QUOTENAME(t.name) ELSE '' END
   + ' TO [' + SP.name + ']' + 
   CASE WHEN SrvPerm.state_desc <> 'GRANT_WITH_GRANT_OPTION' 
       THEN '' 
       ELSE ' WITH GRANT OPTION' 
   END collate database_default AS [-- Server Level Permissions to Be Granted --] 
FROM sys.server_permissions AS SrvPerm 
   INNER JOIN sys.server_principals AS SP 
   ON SrvPerm.grantee_principal_id = SP.principal_id 
   LEFT OUTER JOIN sys.server_principals AS t
   ON SrvPerm.major_id = t.principal_id
WHERE   SP.type IN ( 'S', 'U', 'G' ) 
       AND SP.name NOT LIKE '##%##'
       AND SP.name NOT LIKE 'NT AUTHORITY%'
       AND SP.name NOT LIKE 'NT SERVICE%'
       AND SP.name <> ('sa');

結果：

GRANT CONNECT SQL TO [Radhe]
GRANT VIEW DEFINITION ON LOGIN::[site_helpdesk] TO [Radhe]
GRANT VIEW DEFINITION ON LOGIN::[camper] TO [Radhe]
GRANT VIEW DEFINITION ON LOGIN::[productuser] TO [Radhe]

引用自：https://dba.stackexchange.com/questions/218272