SQL Server 登录名、用户、角色操作语句集合

在 SQL Server 里，用户和角色是管理数据库访问权限的关键概念。用户是访问数据库的主体，而角色则是一组用户权限的集合，通过角色能更高效地管理用户权限。接下来，我会详细介绍如何创建用户和角色、设置用户密码与角色权限、将用户添加到角色以及授予和收回权限。

登录名操作

使用 CREATE USER 语句可以创建新用户，并且可以设置用户密码。以下是一个创建用户的示例：

-- 登录名操作

-- 创建登录名（使用 SQL Server 身份验证）
-- 要求密码满足一定的复杂度要求，例如包含大写字母、小写字母、数字和特殊字符，密码长度至少为 8 位
CREATE LOGIN new_login 
WITH PASSWORD = 'YourPassword123', 
CHECK_POLICY = ON,
CHECK_EXPIRATION = ON;

-- 创建登录名（使用 Windows 身份验证）
CREATE LOGIN [DOMAIN\username] FROM WINDOWS;

-- 修改登录名密码
-- 同时可以修改其他属性，如密码是否过期、是否锁定等
ALTER LOGIN new_login 
WITH PASSWORD = 'NewPassword456' UNLOCK, 
CHECK_EXPIRATION = OFF;

-- 重命名登录名
-- 使用 ALTER LOGIN 替代 sp_rename_login
ALTER LOGIN old_login_name WITH NAME = new_login_name;

-- 删除登录名
-- 在删除登录名之前，建议先确保该登录名在所有数据库中对应的用户已被删除
DROP LOGIN new_login;

-- 查看登录名的属性
SELECT name, is_disabled, create_date, modify_date
FROM sys.server_principals
WHERE type = 'S' AND name = 'new_login';

-- 禁用登录名
ALTER LOGIN new_login DISABLE;

-- 启用登录名
ALTER LOGIN new_login ENABLE;

-- 查看登录名所属的固定服务器角色
SELECT r.name AS role_name
FROM sys.server_role_members m
JOIN sys.server_principals r ON m.role_principal_id = r.principal_id
JOIN sys.server_principals l ON m.member_principal_id = l.principal_id
WHERE l.name = 'new_login';

-- 设置登录名的默认数据库
ALTER LOGIN new_login WITH DEFAULT_DATABASE = new_database;

-- 设置登录名的默认语言
ALTER LOGIN new_login WITH DEFAULT_LANGUAGE = new_language;

用户操作

使用 CREATE ROLE 语句可以创建新角色。以下是一个创建角色的示例：

-- 在当前数据库中创建用户并关联到已有登录名
CREATE USER new_user FROM LOGIN new_login;

-- 修改用户默认架构
ALTER USER new_user WITH DEFAULT_SCHEMA = new_schema;

-- 删除用户
DROP USER new_user;

-- 更改用户关联的登录名
ALTER USER new_user WITH LOGIN = another_login;

-- 查看用户所属的角色
SELECT r.name AS role_name
FROM sys.database_role_members m
JOIN sys.database_principals r ON m.role_principal_id = r.principal_id
JOIN sys.database_principals u ON m.member_principal_id = u.principal_id
WHERE u.name = 'new_user';

-- 查看用户的权限
SELECT *
FROM sys.fn_my_permissions('new_user', 'DATABASE')
WHERE subentity_name  = '';

-- 转移用户拥有的对象到另一个用户
ALTER AUTHORIZATION ON OBJECT::table_name TO new_owner_user;

-- 禁用用户
-- 实际上是通过拒绝用户的 CONNECT 权限来实现禁用效果
DENY CONNECT TO new_user;

-- 启用用户
-- 收回之前拒绝的 CONNECT 权限
REVOKE CONNECT FROM new_user;

角色操作

使用 GRANT 语句可以为角色授予权限。以下是一个为角色授予 SELECT 权限的示例：

-- 创建用户定义数据库角色
-- 可以指定角色的所有者
CREATE ROLE custom_role 
AUTHORIZATION dbo;

-- 将用户添加到角色
ALTER ROLE custom_role 
ADD MEMBER new_user;

-- 从角色中移除用户
ALTER ROLE custom_role 
DROP MEMBER new_user;

-- 删除角色
-- 在删除角色之前，确保该角色不包含任何成员，并且没有被授予任何权限
DROP ROLE custom_role;

-- 将登录名添加到固定服务器角色
EXEC sp_addsrvrolemember 'new_login', 'sysadmin';
-- 从固定服务器角色中移除登录名
EXEC sp_dropsrvrolemember 'new_login', 'sysadmin';

-- 将用户添加到固定数据库角色
EXEC sp_addrolemember 'db_datareader', 'new_user';
-- 从固定数据库角色中移除用户
EXEC sp_droprolemember 'db_datareader', 'new_user';

-- 查看固定服务器角色成员
SELECT *
FROM sys.server_role_members rm
JOIN sys.server_principals p ON rm.member_principal_id = p.principal_id
JOIN sys.server_principals r ON rm.role_principal_id = r.principal_id
WHERE r.name = 'sysadmin';

-- 查看固定数据库角色成员
SELECT *
FROM sys.database_role_members rm
JOIN sys.database_principals p ON rm.member_principal_id = p.principal_id
JOIN sys.database_principals r ON rm.role_principal_id = r.principal_id
WHERE r.name = 'db_datareader';

-- 查看用户定义角色的权限
SELECT *
FROM sys.fn_my_permissions('custom_role', 'DATABASE')
WHERE subentity_name = '';

权限操作

使用 ALTER ROLE 语句可以将用户添加到角色中。以下是一个将用户添加到角色的示例：

-- 为角色授予权限（以对表的 SELECT 权限为例）
GRANT SELECT ON Products TO custom_role;
-- 收回角色的权限
REVOKE SELECT ON Products FROM custom_role;
-- 拒绝角色的权限
DENY SELECT ON Products TO custom_role;

-- 为用户授予对表的 SELECT 权限
GRANT SELECT ON Products TO new_user;
-- 为用户授予对表的 INSERT 权限
GRANT INSERT ON Orders TO new_user;
-- 为用户授予对表的 UPDATE 权限
GRANT UPDATE ON Customers TO new_user;
-- 为用户授予对表的 DELETE 权限
GRANT DELETE ON Employees TO new_user;
-- 为用户授予对表的所有权限
GRANT ALL PRIVILEGES ON Products TO new_user;

-- 为用户授予对表的 SELECT 权限
GRANT SELECT ON Products TO new_user;
-- 为用户授予对表的 INSERT 权限
GRANT INSERT ON Orders TO new_user;
-- 为用户授予对表的 UPDATE 权限
GRANT UPDATE ON Customers TO new_user;
-- 为用户授予对表的 DELETE 权限
GRANT DELETE ON Employees TO new_user;
-- 为用户授予对表的所有权限
GRANT ALL PRIVILEGES ON Products TO new_user;

-- 为用户授予对视图的 SELECT 权限
GRANT SELECT ON vw_ProductSummary TO new_user;

-- 为用户授予对存储过程的 EXECUTE 权限
GRANT EXECUTE ON usp_GetOrderDetails TO new_user;

-- 为用户授予对函数的 EXECUTE 权限（标量值函数）
GRANT EXECUTE ON ufn_GetProductPrice TO new_user;
-- 为用户授予对函数的 SELECT 权限（表值函数）
GRANT SELECT ON ufn_GetProductList TO new_user;

-- 为角色授予对表的 SELECT 权限
GRANT SELECT ON Products TO custom_role;
-- 为角色授予对表的 INSERT 权限
GRANT INSERT ON Orders TO custom_role;
-- 为角色授予对表的 UPDATE 权限
GRANT UPDATE ON Customers TO custom_role;
-- 为角色授予对表的 DELETE 权限
GRANT DELETE ON Employees TO custom_role;
-- 为角色授予对表的所有权限
GRANT ALL PRIVILEGES ON Products TO custom_role;

-- 为角色授予对视图的 SELECT 权限
GRANT SELECT ON vw_ProductSummary TO custom_role;

-- 为角色授予对存储过程的 EXECUTE 权限
GRANT EXECUTE ON usp_GetOrderDetails TO custom_role;

-- 为角色授予对函数的 EXECUTE 权限（标量值函数）
GRANT EXECUTE ON ufn_GetProductPrice TO custom_role;
-- 为角色授予对函数的 SELECT 权限（表值函数）
GRANT SELECT ON ufn_GetProductList TO custom_role;

-- 收回用户对表的 SELECT 权限
REVOKE SELECT ON Products FROM new_user;
-- 收回用户对视图的 SELECT 权限
REVOKE SELECT ON vw_ProductSummary FROM new_user;
-- 收回用户对存储过程的 EXECUTE 权限
REVOKE EXECUTE ON usp_GetOrderDetails FROM new_user;
-- 收回用户对函数的 EXECUTE 权限（标量值函数）
REVOKE EXECUTE ON ufn_GetProductPrice FROM new_user;
-- 收回用户对函数的 SELECT 权限（表值函数）
REVOKE SELECT ON ufn_GetProductList FROM new_user;

-- 收回角色对表的 SELECT 权限
REVOKE SELECT ON Products FROM custom_role;
-- 收回角色对视图的 SELECT 权限
REVOKE SELECT ON vw_ProductSummary FROM custom_role;
-- 收回角色对存储过程的 EXECUTE 权限
REVOKE EXECUTE ON usp_GetOrderDetails FROM custom_role;
-- 收回角色对函数的 EXECUTE 权限（标量值函数）
REVOKE EXECUTE ON ufn_GetProductPrice FROM custom_role;
-- 收回角色对函数的 SELECT 权限（表值函数）
REVOKE SELECT ON ufn_GetProductList FROM custom_role;

-- 拒绝用户对表的 SELECT 权限
DENY SELECT ON Products TO new_user;
-- 拒绝用户对视图的 SELECT 权限
DENY SELECT ON vw_ProductSummary TO new_user;
-- 拒绝用户对存储过程的 EXECUTE 权限
DENY EXECUTE ON usp_GetOrderDetails TO new_user;
-- 拒绝用户对函数的 EXECUTE 权限（标量值函数）
DENY EXECUTE ON ufn_GetProductPrice TO new_user;
-- 拒绝用户对函数的 SELECT 权限（表值函数）
DENY SELECT ON ufn_GetProductList TO new_user;

-- 拒绝角色对表的 SELECT 权限
DENY SELECT ON Products TO custom_role;
-- 拒绝角色对视图的 SELECT 权限
DENY SELECT ON vw_ProductSummary TO custom_role;
-- 拒绝角色对存储过程的 EXECUTE 权限
DENY EXECUTE ON usp_GetOrderDetails TO custom_role;
-- 拒绝角色对函数的 EXECUTE 权限（标量值函数）
DENY EXECUTE ON ufn_GetProductPrice TO custom_role;
-- 拒绝角色对函数的 SELECT 权限（表值函数）
DENY SELECT ON ufn_GetProductList TO custom_role;

注意事项

• 要保证登录名的密码符合 SQL Server 的密码策略要求。
• 若要创建数据库用户，必须先有对应的登录名。
• 权限的授予和收回操作要依据实际的业务需求谨慎进行。

通过以上步骤，你就能够在 SQL Server 中有效地管理用户和角色的权限了。

预定义的角色

在 SQL Server 中，预定义角色分为固定服务器角色和固定数据库角色，下面为你详细介绍这些角色及其权限。

固定服务器角色

固定服务器角色具有服务器级别的管理权限，用于管理 SQL Server 实例。这些角色在服务器安装时就已创建，不能被删除，但可以向其中添加或移除登录名。

1. sysadmin

• 权限：拥有对 SQL Server 实例的完全控制权，可以执行任何操作，包括创建和管理登录名、数据库，以及配置服务器设置等。
• 适用场景：通常只有数据库管理员（DBA）才会被添加到这个角色中，因为该角色权限极大，操作不当可能会对整个 SQL Server 实例造成严重影响。

2. serveradmin

• 权限：可以更改服务器范围的配置选项和关闭服务器。例如，调整内存分配、并发连接数等服务器级别的参数。
• 适用场景：适合负责服务器性能调优和维护的人员，他们需要对服务器的整体配置进行管理。

3. setupadmin

• 权限：可以管理链接服务器和启动过程。链接服务器允许 SQL Server 实例访问其他数据源，如其他 SQL Server 实例、Oracle 数据库等。
• 适用场景：在需要与其他数据源进行交互的企业环境中，负责配置和管理链接服务器的人员会被添加到该角色。

4. securityadmin

• 权限：管理登录名及其属性，包括创建、修改和删除登录名，以及为登录名分配服务器角色。
• 适用场景：用于管理 SQL Server 安全相关的操作，确保只有授权的用户能够访问服务器。

5. processadmin

• 权限：可以管理 SQL Server 中的进程，包括查看和终止用户进程。
• 适用场景：当出现阻塞或性能问题时，DBA 可以使用该角色的权限来排查和解决问题，例如终止长时间运行的查询。

6. dbcreator

• 权限：可以创建、修改、删除和还原任何数据库。
• 适用场景：适合负责数据库创建和维护的人员，如数据库开发人员或 DBA。

7. diskadmin

• 权限：管理磁盘文件，如添加、删除和移动数据库文件。
• 适用场景：在需要对数据库存储进行管理的情况下使用，例如进行磁盘空间扩展或数据迁移。

8. bulkadmin

• 权限：可以执行 BULK INSERT 语句，用于将大量数据从文件快速导入到数据库表中。
• 适用场景：在进行数据加载和迁移时，数据分析师或 ETL 开发人员可能会使用该角色的权限。

固定数据库角色

固定数据库角色的权限范围限定在单个数据库内，用于管理数据库中的对象和操作。这些角色在每个数据库创建时就已存在，不能被删除，但可以向其中添加或移除数据库用户。

1. db_owner

• 权限：拥有对数据库的所有权限，可以执行数据库内的任何操作，包括创建、修改和删除数据库对象，以及管理数据库用户和角色。
• 适用场景：通常由负责数据库整体管理的 DBA 或数据库所有者使用。

2. db_accessadmin

• 权限：可以添加或删除数据库用户、角色成员，并管理数据库的权限。
• 适用场景：用于管理数据库的安全访问，确保只有授权的用户能够访问数据库。

3. db_securityadmin

• 权限：可以管理数据库中的角色和权限，包括授予、撤销和拒绝权限。
• 适用场景：负责数据库安全策略的制定和实施，确保数据库中的数据得到适当的保护。

4. db_ddladmin

• 权限：可以在数据库中执行数据定义语言（DDL）语句，如创建、修改和删除表、视图、存储过程等。
• 适用场景：适合数据库开发人员，他们需要对数据库结构进行设计和修改。

5. db_backupoperator

• 权限：可以备份数据库。
• 适用场景：用于定期备份数据库，以防止数据丢失。通常由负责数据库备份和恢复的人员使用。

6. db_datareader

• 权限：可以读取数据库中所有用户表的数据。
• 适用场景：适用于只需要查询数据而不需要修改数据的用户，如报表生成人员或数据分析人员。

7. db_datawriter

• 权限：可以在数据库中插入、更新和删除所有用户表的数据。
• 适用场景：用于需要对数据库中的数据进行修改的用户，如业务操作人员。

8. db_denydatareader

• 权限：明确拒绝读取数据库中所有用户表的数据。
• 适用场景：用于限制某些用户的查询权限，确保他们无法访问敏感数据。

9. db_denydatawriter

• 权限：明确拒绝在数据库中插入、更新和删除所有用户表的数据。
• 适用场景：用于限制某些用户的数据修改权限，确保数据的完整性和安全性。

通过合理使用这些预定义角色，可以有效地管理 SQL Server 中的用户权限，提高数据库的安全性和可维护性。