2022-01-19

SQL Server Gentleman Services 資料庫紳士密令

說明

SQL Server Gentleman Services 🎩

說明

建立 Login 需要考慮使用者的驗證方式

Windows 驗證
SQL 驗證

USE [master]
GO
-- SQL Login
CREATE LOGIN [loginUser] WITH PASSWORD = N'********',
  DEFAULT_DATABASE=[master],
  CHECK_EXPIRATION=OFF,
  CHECK_POLICY=OFF

-- Windows Login
CREATE LOGIN [domain\loginUser] FROM WINDOWS
  WITH DEFAULT_DATABASE = [master]

Create Login 需要注意保持不同 Availability Group 之間的一致。

因為 Login 是伺服器物件，所以同步建立必須自行完成，可以先於其中一個 Group 完成建立後，使用 EXEC sp_help_revlogin 將 Login 建立到另其他的 Group，安全性不需要重新設定。

⭐SQL Server Restore With Orphaned User Missing User (孤兒 / 孤立使用者)

User

建立 User 需要有對應的 Login：

USE [AdventureWorks]
GO
CREATE USER [userName] FOR LOGIN [loginUser]
ALTER ROLE [db_datareader] ADD MEMBER [userName]
ALTER ROLE [db_datawriter] ADD MEMBER [userName]

Permissions

授權的公式：

授予登入/使用者資料庫伺服器.資料庫.物件權限項目
授予登入/使用者資料庫伺服器.資料庫角色
授予登入/使用者資料庫伺服器角色

🚫 絕不任意給予 db_owner, db_accessadmin, db_securityadmin, db_backoperator Roles

權限依照最小授權的方式進行，例行性的權限可以用 role 的方式給予，例如 db_datareader 與 db_datawriter

ALTER ROLE [db_datareader] ADD MEMBER [userName]
ALTER ROLE [db_datawriter] ADD MEMBER [userName]

對於單一資料庫物件的讀寫權限包含 SELECT, DELETE, UPDATE 及 INSERT：

GRANT SELECT ON dbo.TableName TO User
GRANT DELETE ON dbo.TableName TO User
GRANT UPDATE ON dbo.TableName TO User
GRANT INSERT ON dbo.TableName TO User

如果有需要執行預存程序 (Stored Procedures) 的權限，則針對物件給予 Execute：

GRANT EXECUTE ON OBJECT::dbo.uspSP1 TO User

⭐特殊權限的代理人設定方式 Truncate Permissions Only

Database

建立資料庫需要考慮下列事情：

資料庫命名規則一致性
正式區資料庫不提供 db_owner
開發區資料庫提供 db_owner
應用程式連線帳號僅提供 db_datareader 以及 db_datawriter 權限
資料庫建立後應立即完整備份，作為排程交易紀錄備份的起點
檢查排程備份規則是否包含新資料庫

Objects, Table & Data

資料表設計新增、異動
使用工具同步資料 (Import & Export Wizzard)
建立與修改 View, Stored Procedures, User Functions

⭐SQL Server 批次資料匯入匯出 (BCP, Bulk Insert & OPENROWSET)
⭐SQL Server Table To Table Copy Data 資料表對資料表複製

欄位長度調整

ALTER TABLE Employee ALTER COLUMN EmpName NVARCHAR(400)

Restart Database

ALTER DATABASE DatabaseName SET OFFLINE WITH ROLLBACK IMMEDIATE

WAITFOR DELAY '00:00:30'

ALTER DATABASE DatabaseName SET ONLINE

原理是將資料庫離線在重新上線，以清除所有關於該資料庫的連線，可以在搭配排程達到自動化功能。

Database Backup

如果有使用差異備份，須注意臨時的完整備份必須使用 In-Copy-Only 避免破壞差異備份的基準。

⭐Copy Only 的使用時機

Database Restore

注意是否要覆蓋掉原本的資料庫
注意還原的路徑、檔名 (來源及目的)
還原後須注意孤立使用者的問題

⭐SQL Server Restore With Orphaned User Missing User (孤兒 / 孤立使用者)

資料庫正在還原問題

正在還原其實不是問題，而是資料庫的狀態，可以使用 sys.databases 從 state_desc 欄位看到。其中正常服役的資料庫為 ONLINE，處於正在還原的資料庫則會視 RESTORING。

處於正在還原狀態的資料庫，可以持續接受交易紀錄還原，但無法被讀取。使用下列指令，可以將 RESTORING 的資料庫狀態變更為 ONLINE：

RESTORE DATABASE [DatabaseName] WITH RECOVERY

Restore To Availability Groups Database

停止來源端資料庫

USE [master]
ALTER DATABASE [DatabaseSource] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO

來源端資料庫完整備份
還原至可用性群組資料庫主節點
解決孤立使用者，將 Login 加入至各可用性群組節點
進行首次完整備份，作為交易紀錄基準