# 查看所有固定服务器角色
SELECT name, type_desc, is_fixed_role
FROM sys.server_principals
WHERE type = ‘R’
ORDER BY name;
GO

name type_desc is_fixed_role
————— ———– ————-
bulkadmin SERVER_ROLE 1
dbcreator SERVER_ROLE 1
diskadmin SERVER_ROLE 1
processadmin SERVER_ROLE 1
public SERVER_ROLE 1
securityadmin SERVER_ROLE 1
serveradmin SERVER_ROLE 1
setupadmin SERVER_ROLE 1
sysadmin SERVER_ROLE 1

# 查看服务器角色成员
SELECT sp.name AS RoleName, mp.name AS MemberName, mp.type_desc
FROM sys.server_role_members srm
JOIN sys.server_principals sp ON srm.role_principal_id = sp.principal_id
JOIN sys.server_principals mp ON srm.member_principal_id = mp.principal_id
ORDER BY sp.name, mp.name;
GO

RoleName MemberName type_desc
————- ——————– ————-
sysadmin NT AUTHORITY\SYSTEM WINDOWS_LOGIN
sysadmin fgedu_dba SQL_LOGIN
dbcreator fgedu_dev SQL_LOGIN
bulkadmin fgedu_etl SQL_LOGIN

# 查看特定登录的服务器角色
EXEC sp_helpsrvrolemember ‘sysadmin’;
GO

ServerRole MemberName MemberSID
————- ——————– ——————
sysadmin NT AUTHORITY\SYSTEM 0x0101000000000005…
sysadmin fgedu_dba 0x5E2A3B4C5D6E7F8A…

# 创建自定义服务器角色
CREATE SERVER ROLE fgedu_serveradmin;
GO

Command(s) completed successfully.

# 授予服务器级别权限
GRANT ALTER ANY DATABASE TO fgedu_serveradmin;
GRANT CREATE ANY DATABASE TO fgedu_serveradmin;
GRANT VIEW SERVER STATE TO fgedu_serveradmin;
GO

Command(s) completed successfully.

# 添加成员到服务器角色
ALTER SERVER ROLE fgedu_serveradmin ADD MEMBER fgedu_dba;
GO

Command(s) completed successfully.

# 验证自定义服务器角色
SELECT name, type_desc, is_fixed_role
FROM sys.server_principals
WHERE name = ‘fgedu_serveradmin’;
GO

name type_desc is_fixed_role
——————- ———– ————-
fgedu_serveradmin SERVER_ROLE 0

# 查看自定义服务器角色权限
SELECT permission_name, state_desc
FROM sys.server_permissions
WHERE grantee_principal_id = (
SELECT principal_id FROM sys.server_principals WHERE name = ‘fgedu_serveradmin’
);
GO

permission_name state_desc
———————— ———–
ALTER ANY DATABASE GRANT
CREATE ANY DATABASE GRANT
VIEW SERVER STATE GRANT

# 切换到目标数据库
USE fgedudb;
GO

Changed database context to ‘fgedudb’.

# 查看所有数据库角色
SELECT name, type_desc, is_fixed_role
FROM sys.database_principals
WHERE type = ‘R’
ORDER BY name;
GO

name type_desc is_fixed_role
——————- ————– ————-
db_accessadmin DATABASE_ROLE 1
db_backupoperator DATABASE_ROLE 1
db_datareader DATABASE_ROLE 1
db_datawriter DATABASE_ROLE 1
db_ddladmin DATABASE_ROLE 1
db_denydatareader DATABASE_ROLE 1
db_denydatawriter DATABASE_ROLE 1
db_owner DATABASE_ROLE 1
db_securityadmin DATABASE_ROLE 1
public DATABASE_ROLE 1

# 查看数据库角色成员
SELECT dr.name AS RoleName, dp.name AS MemberName, dp.type_desc
FROM sys.database_role_members drm
JOIN sys.database_principals dr ON drm.role_principal_id = dr.principal_id
JOIN sys.database_principals dp ON drm.member_principal_id = dp.principal_id
ORDER BY dr.name, dp.name;
GO

RoleName MemberName type_desc
————— ———– ————-
db_datareader fgedu_read SQL_USER
db_datareader fgedu_app SQL_USER
db_datawriter fgedu_app SQL_USER
db_owner dbo WINDOWS_USER
db_owner fgedu_dba SQL_USER

# 创建自定义数据库角色
USE fgedudb;
GO

CREATE ROLE fgedu_app_role;
GO

Command(s) completed successfully.

# 授予架构级别权限
GRANT SELECT, INSERT, UPDATE, DELETE ON SCHEMA::dbo TO fgedu_app_role;
GO

Command(s) completed successfully.

# 授予存储过程执行权限
GRANT EXECUTE ON SCHEMA::dbo TO fgedu_app_role;
GO

Command(s) completed successfully.

# 创建只读角色
CREATE ROLE fgedu_readonly_role;
GO

Command(s) completed successfully.

GRANT SELECT ON SCHEMA::dbo TO fgedu_readonly_role;
GO

Command(s) completed successfully.

# 创建报表角色
CREATE ROLE fgedu_report_role;
GO

Command(s) completed successfully.

GRANT SELECT ON SCHEMA::dbo TO fgedu_report_role;
GRANT VIEW DEFINITION ON SCHEMA::dbo TO fgedu_report_role;
GO

Command(s) completed successfully.

# 验证自定义角色
SELECT name, type_desc, is_fixed_role
FROM sys.database_principals
WHERE type = ‘R’ AND is_fixed_role = 0;
GO

name type_desc is_fixed_role
——————- ————– ————-
fgedu_app_role DATABASE_ROLE 0
fgedu_readonly_role DATABASE_ROLE 0
fgedu_report_role DATABASE_ROLE 0

# 创建数据库用户
USE fgedudb;
GO

CREATE USER fgedu_app FOR LOGIN fgedu_app;
GO

Command(s) completed successfully.

# 添加用户到角色
ALTER ROLE db_datareader ADD MEMBER fgedu_app;
ALTER ROLE db_datawriter ADD MEMBER fgedu_app;
GO

Command(s) completed successfully.

# 授予对象级别权限
GRANT SELECT, INSERT, UPDATE ON fgedu_orders TO fgedu_app;
GO

Command(s) completed successfully.

# 授予列级别权限
GRANT SELECT (order_id, customer_name, order_date) ON fgedu_orders TO fgedu_report;
GO

Command(s) completed successfully.

# 授予存储过程执行权限
GRANT EXECUTE ON fgedu_get_orders TO fgedu_app;
GO

Command(s) completed successfully.

# 授予WITH GRANT OPTION(允许传递权限)
GRANT SELECT ON fgedu_products TO fgedu_lead WITH GRANT OPTION;
GO

Command(s) completed successfully.

# 查看用户权限
SELECT
USER_NAME(grantee_principal_id) AS Grantee,
permission_name,
state_desc,
OBJECT_NAME(major_id) AS ObjectName
FROM sys.database_permissions
WHERE USER_NAME(grantee_principal_id) = ‘fgedu_app’;
GO

Grantee permission_name state_desc ObjectName
——— ————— ———- —————
fgedu_app SELECT GRANT fgedu_orders
fgedu_app INSERT GRANT fgedu_orders
fgedu_app UPDATE GRANT fgedu_orders
fgedu_app EXECUTE GRANT fgedu_get_orders

# 撤销对象权限
REVOKE SELECT, INSERT, UPDATE ON fgedu_orders FROM fgedu_app;
GO

Command(s) completed successfully.

# 拒绝权限(DENY优先级最高)
DENY DELETE ON fgedu_orders TO fgedu_app;
GO

Command(s) completed successfully.

# 从角色移除用户
ALTER ROLE db_datawriter DROP MEMBER fgedu_app;
GO

Command(s) completed successfully.

# 撤销架构权限
REVOKE SELECT ON SCHEMA::dbo FROM fgedu_app;
GO

Command(s) completed successfully.

# 撤销WITH GRANT OPTION权限
REVOKE GRANT OPTION FOR SELECT ON fgedu_products FROM fgedu_lead;
GO

Command(s) completed successfully.

# 查看权限变更后的状态
SELECT
USER_NAME(grantee_principal_id) AS Grantee,
permission_name,
state_desc,
OBJECT_NAME(major_id) AS ObjectName
FROM sys.database_permissions
WHERE USER_NAME(grantee_principal_id) = ‘fgedu_app’;
GO

Grantee permission_name state_desc ObjectName
——— ————— ———- —————
fgedu_app DELETE DENY fgedu_orders
fgedu_app EXECUTE GRANT fgedu_get_orders