[SQL] 權限設定

論壇問題：原 PO 對 db_datareader 和 db_datawriter 權限設定有疑慮，剛好拿來練習並驗證

建立 Login (LoginWrite) 和 User (UserWrite) 對 AdventureWorks2014 內的 Demo Table 進行不同權限設定的 SELECT、INSERT、UPDATE 和 DELETE，藉此觀察權限設定情況

USE [AdventureWorks2014]
GO

IF OBJECT_ID('Demo') IS NOT NULL
   DROP TABLE Demo

-- 建立一個簡單 Table 來驗證，是否具備 Read 或 Write 權限
CREATE TABLE [dbo].[Demo](
   [ID] [int] NULL
) ON [PRIMARY]
GO

IF EXISTS
  (
    SELECT 1
    FROM sys.database_principals
    WHERE Name = 'UserWrite'
  )
  DROP USER UserWrite

IF EXISTS 
  (
    SELECT 1 FROM  sys.sql_logins WHERE Name = 'LoginWrite'
  )
  DROP LOGIN LoginWrite


-- 建立 Login
CREATE LOGIN [LoginWrite] 
  WITH PASSWORD = N'P@ssw0rd', 
  DEFAULT_DATABASE = [AdventureWorks2014], 
  DEFAULT_LANGUAGE = [繁體中文], 
  CHECK_EXPIRATION = OFF, 
  CHECK_POLICY = OFF
GO

-- 建立 User 並指定預設 Schema
CREATE USER [UserWrite] FOR LOGIN [LoginWrite] WITH DEFAULT_SCHEMA = [dbo]
GO

-- 把 UserWrite 加入 db_datareader 內
EXEC sys.sp_addrolemember 'db_datareader' , UserWrite

-- 確認目前的 Login 和 User：
-- SUSER_SNAME()：WIN2012R2\Administrator
-- USER_NAME()  ：dbo
SELECT 
  SUSER_SNAME() , -- Instance Level
  USER_NAME()     -- DB Level

-- 切換 Login 來進行測試
EXECUTE AS LOGIN  = 'LoginWrite'

-- 確認目前的 Login 和 User：
-- SUSER_SNAME()：LoginWrite
-- USER_NAME()  ：UserWrite
SELECT 
  SUSER_SNAME() , -- Instance Level
  USER_NAME()     -- DB Level

-- 權限測試
-- 對 Demo Table 進行 Select、Insert、Update 和 Delete
SELECT * FROM [AdventureWorks2014].[dbo].[Demo]
INSERT INTO [AdventureWorks2014].[dbo].[Demo] (ID) VALUES(1)
DELETE FROM [AdventureWorks2014].[dbo].[Demo]
UPDATE [AdventureWorks2014].[dbo].[Demo] SET ID = 2

-- 測試訊息
--(0 個資料列受到影響)
--訊息 229，層級 14，狀態 5，行 65
--結構描述 'dbo'，資料庫 'AdventureWorks2014'，物件 'Demo' 沒有 INSERT 權限。
--訊息 229，層級 14，狀態 5，行 66
--結構描述 'dbo'，資料庫 'AdventureWorks2014'，物件 'Demo' 沒有 DELETE 權限。
--訊息 229，層級 14，狀態 5，行 67
--結構描述 'dbo'，資料庫 'AdventureWorks2014'，物件 'Demo' 沒有 UPDATE 權限。

-- 返回 WIN2012R2\Administrator Login，後續要對權限進行設定 LoginWrite 和 UserWrite 並沒有授給權限的權利
REVERT

-- 確認目前的 Login 和 User：
-- SUSER_SNAME()：WIN2012R2\Administrator
-- USER_NAME()  ：dbo
SELECT 
  SUSER_SNAME() , -- Instance Level
  USER_NAME()     -- DB Level

-- 授給 UserWrite 對 Demo Table 進行 INSERT 的權限
GRANT SELECT , INSERT
ON AdventureWorks2014.dbo.Demo
TO [UserWrite]
 
 -- 切換 Login
EXECUTE AS LOGIN  = 'LoginWrite'

-- 權限測試
-- 對 Demo Table 進行 Select、Insert、Update 和 Delete
SELECT * FROM [AdventureWorks2014].[dbo].[Demo]
INSERT INTO [AdventureWorks2014].[dbo].[Demo] (ID) VALUES(1)
DELETE FROM [AdventureWorks2014].[dbo].[Demo]
UPDATE [AdventureWorks2014].[dbo].[Demo] SET ID = 2

-- 測試訊息
--(0 個資料列受到影響)
--(1 個資料列受到影響)
--訊息 229，層級 14，狀態 5，行 96
--結構描述 'dbo'，資料庫 'AdventureWorks2014'，物件 'Demo' 沒有 DELETE 權限。
--訊息 229，層級 14，狀態 5，行 97
--結構描述 'dbo'，資料庫 'AdventureWorks2014'，物件 'Demo' 沒有 UPDATE 權限。

-- 返回 WIN2012R2\Administrator Login，後續要對權限進行設定 LoginWrite 和 UserWrite 並沒有授給權限的權利
REVERT

-- 把 UserWrite 加入 db_denydatawriter 內
EXEC sys.sp_addrolemember 'db_denydatawriter' , UserWrite

-- 切換至 LoginWrite
EXECUTE AS LOGIN  = 'LoginWrite'

-- 權限測試
-- 對 Demo Table 進行 Select、Insert、Update 和 Delete
SELECT * FROM [AdventureWorks2014].[dbo].[Demo]
INSERT INTO [AdventureWorks2014].[dbo].[Demo] (ID) VALUES(1)
DELETE FROM [AdventureWorks2014].[dbo].[Demo]
UPDATE [AdventureWorks2014].[dbo].[Demo] SET ID = 2

-- 測試訊息
--(0 個資料列受到影響)
--訊息 229，層級 14，狀態 5，行 117
--結構描述 'dbo'，資料庫 'AdventureWorks2014'，物件 'Demo' 沒有 INSERT 權限。
--訊息 229，層級 14，狀態 5，行 118
--結構描述 'dbo'，資料庫 'AdventureWorks2014'，物件 'Demo' 沒有 DELETE 權限。
--訊息 229，層級 14，狀態 5，行 119
--結構描述 'dbo'，資料庫 'AdventureWorks2014'，物件 'Demo' 沒有 UPDATE 權限。

參考資料
• 論壇問題出處
• 論壇討論：Login 和 User 差異
• SQL Server 伺服器角色/資料庫角色/權限給予