SQL Server Gentleman Services 🎩
說明
Login
建立 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 避免破壞差異備份的基準。
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 加入至各可用性群組節點
- 進行首次完整備份,作為交易紀錄基準