五餅二魚工作室

在 SQL Server 的管理中，除了 Login ( 登入帳號 ) 可以設定 Server 等級的權限外，有些時候我們會在資料庫內，針對某一個特定的 User ( 使用者 ) 也去設定權限。原本這應該是個很單純的事情，剛好在今天救火的過程中，遇到一些狀況，因此做個筆記整理一下。

原本在某一個資料庫內，有類似以下的狀況去設定一些權限給 test 的使用者

但因為一個功能上的改變，需要去調整一些結構，因此可能會下了一些類似的指令

USE [TEST1]
GO

/****** Object:  Table [dbo].[T1] ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[T1]') AND type in (N'U'))
DROP TABLE [dbo].[T1]
GO

/****** Object:  Table [dbo].[T1] ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[T1](
	[F1] [nchar](10) PRIMARY KEY,
	[F2] INT,
) ON [PRIMARY]
GO

上述指令只是個示意，因為畢竟資料表內有資料，因此可能會先將資料表給更名，然後建立一個新的資料表，把資料從更名後的資料表轉到新的資料表，然後刪除舊的資料表，類似這樣的行為。但不論是簡單的處理或者是複雜的處理，當我們將原本的物件給刪除，再重新建立新的物件的時候，即便名稱都還是維持一樣，但原本相關使用者或者是角色，對於該物件上所設定的權限，都會消失掉。

但有朋友反映說，可是當我們使用 SSMS 去修改資料表結構的時候，那為什麼權限還會保留著呢 ? 因此我們也做了一個類似的案例去測試的時候，如果用 SQL Profiler 去攔 SQL 指令的話，其實 SSMS 會用類似以下的指令

select 
	col.name, user_name(per.grantee_principal_id) as Grantee_Name, user_name(per.grantor_principal_id) as Grantor_name, 
	per.type, per.state 
from sys.database_permissions  per 
left outer join sys.columns col on (per.class = 1 and per.major_id = col.object_id and per.minor_id > 0 and per.minor_id = col.column_id) 
where per.class = 1 and per.major_id = object_id(N'dbo.T1') 
order by per.state, per.type, per.grantor_principal_id, per.grantee_principal_id 

當他要去修改資料表的時候，會先用類似上述的指令，將您要改變的物件上所有設定的權限先記錄起來，然後當重建好新的物件之後，再重新用之前所取得的權限資訊，幫您將物件的權限重新設定上去。

既然我們知道了 SSMS 是這樣做，因此我們也可以調整一下類似的語法，改成類似以下的方式

WITH A AS
(
SELECT  CASE WHEN per.state <> 'W' THEN per.state_desc ELSE 'GRANT' END +' ' + 
        per.permission_name + ' ON OBJECT::' + OBJECT_NAME(per.major_id) + ' TO ' + QUOTENAME(dpg.name) COLLATE database_default AS Command
		,per.class as Class,  QUOTENAME(dpg.name) as Name
FROM    sys.database_permissions AS per
INNER JOIN sys.database_principals AS dpg ON per.grantee_principal_id = dpg.principal_id
WHERE   per.major_id > 0 AND per.class = 1
UNION ALL
SELECT  CASE WHEN per.state <> 'W' THEN per.state_desc ELSE 'GRANT' END +' ' + 
        per.permission_name + ' ON SCHEMA::' + SCHEMA_NAME(per.major_id) + ' TO ' + QUOTENAME(dpg.name) COLLATE database_default AS Command
		,per.class as Class,  QUOTENAME(dpg.name) as Name
FROM    sys.database_permissions AS per
INNER JOIN sys.database_principals AS dpg ON per.grantee_principal_id = dpg.principal_id
WHERE   per.major_id > 0 AND per.class = 3
)
SELECT Command FROM A 
ORDER BY Name,Class

用這樣的指令將設定在 OBJECT (物件) 或 SCHEMA (結構描述) 上的權限，轉為相對應的指令碼，這樣當後續如果有物件變更，就可以用原本存起來的腳本再執行一次，就可以還原權限了。

---

原本想說這樣就可以交差了，但朋友還是詢問有沒有更便捷的方式，畢竟很多時候會有新增加的物件，或者是有人遺漏設定，等發現的時候又很難補上相關權限了。其實我自己很少針對每個物件去設定權限，如果真的要設定，我還是比較建議採用 SCHEMA 去做設定，就類似以下的指令

GRANT DELETE ON SCHEMA::dbo TO [test]

當我賦予使用者針對 dbo 這個結構描述上有刪除的權限，這樣不管後續在 dbo 下面有增加那些物件，這個使用者都可以對那個物件可以下達刪除的指令。

而如果還要更好，那就是再去搭配「使用者定義資料庫角色」的方式，將權限指定給一個角色之後，再將使用者加入到某個角色，這樣設定起來也就可以更加彈性跟方便了。