SQL Server - 如何列出目前已賦予或限制的權限清單

        資料庫除了要有良好的處理效能外，安全性也是非常重要的一環，而在資料庫中主要透過  GRANT 與 REVOKE 來賦予使用者權限或限制存取的權限，而這兩個指令的用法，我就不在這裡多說，您可以參考下列的連結，但是當我希望可以進行安全性控管時，需要每日監控有那些已賦序或已限制的清單時，總不能透過逐一統計的方式，所以我提供了下列的語法，大家如果有針對使用者進行權限的 GRANT 或 REVOKE時，就可以透過下列的指令列出目前的資料庫中已設定的清單。
範例說明：
假設我有一個資料庫名稱為 [cary_test] ，其中有一個使用者名稱為 [test_user]，我希望此使用者針對 [test] 的資料表只能有新增，但不能有刪除的功能。

1、選擇 [test_user]的使用者。

2、設定 GRANT -> INSERT，REVOKE -> DELETE

3、透過下列的語法進行查詢，即可得到 GRANT 與 REVOKE的清單。

4、上述的安全性稽核清單，您需要每天進行確認時，您可以使用 Reporting Service 作成報表後，透過訂閱的方式進行寄送，這樣就可以每天在固定的時間寄送到您的信箱了。

查詢語法：
USE cary_test;

SELECT [ROLE] = su.name, [OBJECT] = so.name,
[OBJECT TYPE] = CASE so.type
WHEN 'C' THEN 'CHECK constraint'
WHEN 'D' THEN 'Default or DEFAULT constraint'
WHEN 'F' THEN 'FOREIGN KEY constraint'
WHEN 'FN' THEN 'Scalar function'
WHEN 'IF' THEN 'Inlined table-function'
WHEN 'K' THEN 'PRIMARY KEY or UNIQUE constraint'
WHEN 'L' THEN 'Log'
WHEN 'P' THEN 'Stored procedure'
WHEN 'R' THEN 'Rule'
WHEN 'RF' THEN 'Replication filter stored procedure'
WHEN 'S' THEN 'System table'
WHEN 'TF' THEN 'Table function'
WHEN 'TR' THEN 'Trigger'
WHEN 'U' THEN 'User table'
WHEN 'V' THEN 'View'
WHEN 'X' THEN 'Extended stored procedure'
ELSE 'OTHER'
END, sc.name 'COLUMN NAME', 
[ACTION] = CASE sp.action
WHEN 26 THEN 'REFERENCES'
WHEN 178 THEN 'CREATE FUNCTION'
WHEN 193 THEN 'SELECT'
WHEN 195 THEN 'INSERT'
WHEN 196 THEN 'DELETE'
WHEN 197 THEN 'UPDATE'
WHEN 198 THEN 'CREATE TABLE'
WHEN 203 THEN 'CREATE DATABASE'
WHEN 207 THEN 'CREATE VIEW'
WHEN 222 THEN 'CREATE PROCEDURE'
WHEN 224 THEN 'EXECUTE'
WHEN 228 THEN 'BACKUP DATABASE'
WHEN 233 THEN 'CREATE DEFAULT'
WHEN 235 THEN 'BACKUP LOG'
WHEN 236 THEN 'CREATE RULE'
ELSE 'OTHER'
END,
[PROTECT TYPE] = CASE sp.protecttype
WHEN 204 THEN 'GRANT_W_GRANT'
WHEN 205 THEN 'GRANT'
WHEN 206 THEN 'REVOKE'
ELSE 'OTHER'
END,
[PROTECTION APPLIES] = CASE
WHEN sp.columns = 1 THEN 'ALL COLUMNS'
WHEN sp.columns > 1 THEN 'SPECIFIC COLUMNS'
ELSE 'N/A'
END
FROM sysprotects sp
INNER JOIN sysusers su ON sp.uid = su.uid
INNER JOIN sysobjects so ON so.id = sp.id
INNER JOIN syscolumns sc ON so.id = sc.id
ORDER BY su.name, so.name, sp.action

參考連結：
GRANT Server Permissions (Transact-SQL)
http://msdn.microsoft.com/en-us/library/ms186717.aspx
REVOKE Server Permissions (Transact-SQL)
http://msdn.microsoft.com/en-us/library/ms186308.aspx

關鍵字：SQL Server、GRANT、REVOKE、SECURITY、Permission