SQL Server 資料庫的權限設定

通常公司級的資料庫都有權限設定
有時某些人是可以查某些表
有時某個部門不可以進入機敏資料庫等等

這時就需要權限的設定
這一部份資料量挺多的
上課時用老師註解打在指令裡，我加一點說明

SELECT * FROM fn_builtin_permissions(NULL)       --列出所有權限操作指令
SELECT * FROM fn_builtin_permissions('server')   --列出Server等級的權限操作指令
SELECT * FROM fn_builtin_permissions('database') --列出database等級的權限操作指令
SELECT * FROM fn_builtin_permissions('object')   --列出物件操作指令

------------------------------------------------------------------------
--伺服器等級
GRANT CONTROL SERVER TO Mickey  --等同sysadmin，    權限最大，任何事都能做
GRANT ALTER ANY LOGIN TO Mickey  --等同securityadmin 可以建、改連入帳號
GRANT ALTER ANY DATABASE TO Mickey  --等同dbcreator 可以建、改database
GRANT CONNECT SQL TO Mickey   --賦予此登入帳號可以連接至SQL Server

------------------------------------------------------------------------
--資料庫等級
GRANT CONTROL TO Mouse   --等同db_owner
GRANT SELECT TO Mouse   --等同db_datareader
GRANT INSERT TO Mouse   --可在資料庫中INSERT任何資料
GRANT EXECUTE TO Mouse   --可在資料庫中EXECUTE任何預存程序
GRANT CREATE TABLE TO Mouse   --可在資料庫中建立任何資料表(也只能建立，且只有Table)
GRANT ALTER ANY USER TO Mouse  --等同db_accessadmin

------------------------------------------------------------------------
--結構層級
--有時結構下會有很多table、PROCEDURE、VIEW等等
--一個個去賦予權限實在太累了，可以直接用SCHEMA::結構
GRANT SELECT ON SCHEMA::人事 TO Mouse --可 SELECT 人事 SCHEMA 中的任何資料


------------------------------------------------------------------------
--物件等級
GRANT EXECUTE ON 建一堆表 TO Mouse    --賦予 「建一堆表」PROCEDURE 給使用者Mouse
GRANT SELECT,INSERT ON 巨巨 TO Mouse

------------------------------------------------------------------------
REVOKE ALL ON 巨巨 TO Mouse --拿掉Mouse在巨巨表中的所有權限
REVOKE ALL ON 巨巨 TO Minnie --拿掉Minnie在巨巨表中的所有權限
REVOKE ALL ON 匠匠 TO Mouse
REVOKE ALL ON 匠匠 TO Minnie



------------------------------------------------------------------------
--當權限時間不斷有使用者被賦予、取消，有時會挺混亂的
--這時可以用sys.database_permissions配合sys.database_principals來查
SELECT * FROM sys.server_principals    --查當前伺服器中的登入/角色狀態，依人去查


SELECT * FROM sys.database_permissions --查當前資料庫中的所有權限狀態，依權去查
SELECT * FROM sys.database_principals  --查當前資料庫中的使用者/角色狀態，依人去查


--將上2個表結合後，即可查出 某人 在 某表 有 什麼權限
SELECT A.class_desc AS 等級
 ,OBJECT_NAME(major_id) AS 物件名
 ,B.name AS 被賦予帳號
 ,(SELECT name FROM sys.database_principals WHERE principal_id=A.grantor_principal_id) AS 授權帳號
 ,permission_name AS 權限類型
 ,state_desc AS 允許_不允許
FROM sys.database_permissions AS A
JOIN sys.database_principals AS B 
ON a.grantee_principal_id = B.principal_id
WHERE A.major_id> = 0            --小於0的為系統使用者or角色


------------------------------------------------------------------------
--反向配置(權限衝突)
--在SQL Server中「禁止」權限，大於賦予權限
GRANT SELECT ON SCHEMA::dbo TO Minnie --允許Minnie查dbo結構下的所有資料
DENY SELECT ON dbo.AAA TO Minnie      --除了 dbo.AAA 不行!

CREATE LOGIN [MB-206-TEACHER\經理群] FROM WINDOWS    --所有的經理都可以登入
CREATE LOGIN [MB-206-TEACHER\會計經理] FROM WINDOWS  --獨會計經理不行，先為他建一個帳號
DENY CONNECT SQL TO [MB-206-TEACHER\會計經理]        --將會計經理不允許登入