您现在的位置：程式碼之家 >> 數據庫知識 > >> SQL Server 資料庫 >> 關於SqlServer

SQL Server 2005 安全配置進階：T-SQL 建立帳號、角色與權限完整語法

編輯：關於SqlServer

前言

承上篇圖形化操作，本文提供 T-SQL 腳本實現自動化部署與版本控制。涵蓋 Windows/SQL 登入管理、伺服器/資料庫角色、權限授予/拒絕/回收完整語法。

一、登入帳號管理

Windows 驗證登入

-- 授與登入權限
sp_grantlogin 'domain\user';
sp_grantlogin 'domain\group';
sp_grantlogin 'BUILTIN\Users';

-- 拒絕登入
sp_denylogin 'domain\user';
sp_denylogin 'BUILTIN\Users';

-- 移除登入
sp_revokelogin 'domain\user';

SQL Server 驗證登入

-- 建立登入
EXEC sp_addlogin 'login', 'password', 'default_db', 'language', 'sid', 'encryption_option';
-- 範例
EXEC sp_addlogin 'test1';
EXEC sp_addlogin 'test2', 'password', 'northwind';

-- 修改密碼、預設資料庫、預設語言
EXEC sp_password 'oldpwd', 'newpwd', 'login';
EXEC sp_defaultdb 'login', 'database';
EXEC sp_defaultlanguage 'login', 'language';

-- 刪除登入
EXEC sp_droplogin 'login';

二、資料庫角色管理

自訂角色

-- 建立角色
sp_addrole 'role_name', 'owner';  -- owner 預設 dbo
-- 範例
USE northwind;
sp_addrole 'sales', 'dbo';
sp_addrole 'manager';
sp_droprole 'manager';

固定伺服器角色成員管理

sp_addsrvrolemember 'login', 'role';  -- 如 sysadmin, securityadmin
sp_dropsrvrolemember 'login', 'role';

資料庫角色成員管理

sp_addrolemember 'role', 'account';   -- role 為固定或自訂角色
sp_droprolemember 'role', 'account';

資料庫使用者授權存取

sp_grantdbaccess 'login', 'name_in_db';  -- name_in_db 預設同登入名
sp_revokedbaccess 'name_in_db';

三、權限控制 (GRANT / DENY / REVOKE)

-- 授與所有使用者查詢訂單表
USE northwind;
GRANT SELECT ON 訂單 TO public;

-- 授與自訂角色 salesmanager 修改 sales 表
GRANT INSERT, UPDATE, DELETE ON sales TO salesmanager;

-- 授與 user1 查看 publishers 全表，但僅能修改 pub_id 欄
GRANT SELECT, UPDATE(pub_id) ON publishers TO user1;