SQL 問題彙編（一）：SQL Server 6.5 升級 7.0 實戰避坑指南與安全架構最佳實踐

升級陷阱：別名轉角色的隱形邏輯

SQL Server 6.5 → 7.0 升級中，別名自動轉換為角色成員：

• dbo 別名 → db_owner 固定角色成員
• 多個 sales 別名登錄 → 自動建立 sales 角色並納入用戶
• 原別名權限 → 遷移至對應角色
• 系統權限模式位去重清理

災難場景：跨機器數據庫合併後升級

將 10 個異構服務器數據庫恢復至單機再升級：

• 6.5 要求用戶匹配 master..syslogins 與各庫 sysusers
• 跨機恢復導致 SID 不匹配，安全體系損壞
• 升級至 7.0 後損壞依存

破解鑰匙：升級前確保 6.5 系統健康，運行 sp_change_users_login 修復用戶-登錄映射。

安全數據庫構建三板斧

1. 拒絕直接表訪問

• 僅授予 View/Stored Procedure 權限
• 若 SP/Table/View 均屬 dbo，授予 EXECUTE 即可，無需檢查表權限

2. 視圖作為安全閘道

• 可授予 SELECT/INSERT/UPDATE/DELETE 細粒度權限
• SP 僅能授予 EXECUTE
• 在 SP 內嵌業務邏輯控制字段/行級訪問

3. 跨庫查詢的所有權鏈陷阱

pubs（sa 擁有）無法直接連接 NT 登錄擁有的庫，即使雙方均為 sysadmin。解法：統一庫擁有者或使用 sp_changedbowner 變更。

新特性：DENY 語句——禁止權限的利劍

• 6.5 需雙重 REVOKE 模擬，7.0 原生支援
• 慎用陷阱：用戶屬多組時，任一組被 DENY 即生效覆蓋授予權限
• 嚴禁對 PUBLIC 角色使用 DENY（全員隸屬）
• 可用於登錄層面：精準封禁離職員工 Joe 而不影響同組他人

應用程序角色：業務邏輯隔離的安全容器

場景：薪資應用允許更新薪資，但禁止用戶直接登錄修改自己薪資。

1. 應用以用戶身份登錄（審計追蹤）
2. 調用 sp_setapprole 'role_name', 'password' 激活應用角色（密碼可加密傳輸）
3. 用戶原有權限關閉，應用角色權限開啟
4. 連接級生效：多連接需逐個激活，連接建立後首要動作即調用 sp_setapprole