[SQL SERVER] 查詢特定使用者在特定資料庫所被賦予的權限

說明：
        查詢特定使用者在特定資料庫所被賦予的權限，透過語法一查出被賦予的權限，透過語法二直接組成GRANT語法

語法一：
USE database_name
GO
SELECT
State_Desc, Permission_Name, class_desc,
COALESCE(OBJECT_NAME(major_id),DB_NAME(major_id)) SecurableName, SCHEMA_NAME(O.schema_id) [Schema],
Grantees.Name GranteeName, Grantees.Type_Desc GranteeType
FROM sys.database_permissions Perms
INNER JOIN sys.database_principals Grantees ON Perms.Grantee_Principal_Id = Grantees.Principal_Id
LEFT OUTER JOIN sys.all_objects O ON Perms.major_id = O.object_id
where Grantees.Name = 'user_name'
ORDER BY SecurableName

 執行結果：

語法二：
USE database_name
GO
SELECT
State_Desc +' ' +
Permission_Name + ' ON '+
SCHEMA_NAME(O.schema_id) + '.' +
COALESCE(OBJECT_NAME(major_id),DB_NAME(major_id)) collate SQL_Latin1_General_CP1_CI_AS  + ' TO ' +
Grantees.Name GranteeName
FROM sys.database_permissions Perms
INNER JOIN sys.database_principals Grantees ON Perms.Grantee_Principal_Id = Grantees.Principal_Id
LEFT OUTER JOIN sys.all_objects O ON Perms.major_id = O.object_id
where Grantees.Name = 'user_name'
and Permission_Name not in ('CONNECT')

執行結果：
GRANT DELETE ON dbo.Files TO user_name
GRANT INSERT ON dbo.Files TO user_name
GRANT SELECT ON dbo.Files TO user_name
GRANT EXECUTE ON dbo.OLCTSaveFile TO user_name