[SQL Server] 查詢資料庫各資料表的權限清單

在做資料庫的管理事項中, 有時會需要列出資料庫中各個表格的權限清單, 以確認資料表的權限沒有被別人亂設定. 所以為了方便進行這樣的查詢作業, 以下參考一些 SQL Server 既有的 Stored procedure, 將其包裝成一個  SQL Script.
參考資源:

• sp_tables: 傳回目前環境中所能查詢的物件清單。這表示可出現在 FROM 子句中的任何物件，但同義字物件除外。
• sp_table_privileges: 傳回一或多份指定資料表的資料表權限清單 (如 INSERT、DELETE、UPDATE、SELECT、REFERENCES)。
• table 類型的變數宣告.

表格變數的宣告只要依據線上文件中, [結果集] 所列出的資料行名稱與類型宣告即可.
SQL Script 第一行的 "USE [master]" 是表示要查 master 資料庫中所有資料表的權限, 使用者可以依據目的, 修改要查詢的資料庫名稱.

USE [master]
GO
-- 宣告一個存放資料表清單的 table 變數
DECLARE @tables Table
( 
  TABLE_QUALIFIER sysname, 
  TABLE_OWNER sysname,
  TABLE_NAME sysname,
  TABLE_TYPE varchar(32),
  REMARKS varchar(254) 
)

-- 宣告一個存放資料表權限清單的 table 變數
DECLARE @privilege_tables Table
( 
  TABLE_QUALIFIER sysname, 
  TABLE_OWNER sysname,
  TABLE_NAME sysname,
  GRANTOR sysname,
  GRANTEE sysname,
  PRIVILEGE sysname,
  IS_GRANTABLE sysname
)

-- 將 sp_tables 的結果匯入至 @tables 變數
INSERT INTO @tables
EXEC sp_tables

DECLARE @TabletName sysname
-- 查出 TABLE_TYPE 為 'TABLE' 的資料列
DECLARE temp_cursor CURSOR FOR
SELECT TABLE_NAME
FROM @tables t
WHERE TABLE_TYPE='TABLE'

OPEN temp_cursor

FETCH NEXT FROM temp_cursor
INTO @TabletName
WHILE @@FETCH_STATUS = 0
BEGIN
-- 將資料表名稱代入 sp_table_privileges, 並將結果匯入至 @privilege_tables 變數
  INSERT INTO @privilege_tables
  EXEC sp_table_privileges @TabletName
  FETCH NEXT FROM temp_cursor
  INTO @TabletName
END
CLOSE temp_cursor
DEALLOCATE temp_cursor

-- 查詢 dbo 以外的權限
SELECT *
FROM @privilege_tables pt
WHERE GRANTEE<>'dbo'