問題:

今天群里有位同學提問:我怎樣可以給某一用戶在dbo架構中創建視圖的最小權限?

-------------------------------------------------------------------------------------------

第一想法很簡單無非是:

CREATE ROLE MyRole; GO GRANT CREATE VIEW TO MyRole; GO CREATE USER MyUser WITHOUT LOGIN; GOEXEC sp_addrolemember 'MyRole', 'MyUser'; GOEXECUTE AS USER = 'MyUser'; GOCREATE VIEW dbo.V_TESTASSELECT 1 AAGOREVERTGO

但事實是,如果僅僅這樣授權,該用戶會收到這樣的錯誤信息:消息 2760，級別 16，狀態 1，過程 V_TEST，第 9 行The specified schema name "dbo" either does not exist or you do not have permission to use it.

錯誤的原因是該用戶沒有修改架構的權限.于是添加修改dbo架構的授權:

GRANT ALTER ON SCHEMA::dbo TO MyRole;GOCREATE VIEW dbo.V_TESTASSELECT 1 AAGOREVERTGO

這次創建視圖執行成功了.但新的問題來了.由于用戶擁有了修改dbo架構的權限,用戶也就有了修改表/視圖/存儲過程/函數等的權限

,甚至擁有了刪除這些對象的權限，通過剛才的授權，下面的語句就能執行成功:

EXECUTE AS USER = 'MyUser'; GO ALTER TABLE dbo.MyTable ADD AnotherID INT NULL; GO REVERT; GO   EXECUTE AS USER = 'MyUser'; GO DROP TABLE dbo.MyTable; GO REVERT; GO 

要命的是，我們不能簡單通過授權來禁止用戶在某一架構下所有對象的刪除和修改權限。

-------------------------------------------------------------------------------------------

最終解決方案：使用數據庫級別的DDL觸發器來取消某一角色用戶的刪除/修改相關對象的事物。

CREATE TRIGGER [trig_db_BlockDropObjcect]ON DATABASE FOR  DROP_TABLE,DROP_VIEW,DROP_PROCEDURE,DROP_FUNCTION,DROP_CONTRACT,DROP_DEFAULT,DROP_SYNONYM,DROP_TYPE    ,ALTER_TABLE,ALTER_PROCEDURE,ALTER_FUNCTIONAS BEGIN    IF IS_MEMBER('MyRole') = 1    BEGIN        PRINT 'You are not authorized to alter or drop this object.'; 　　　　--僅保留修改視圖權限       ROLLBACK TRANSACTION;    END; END;