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

生命不息-学无止境

于 2025-04-29 23:26:56 发布

阅读量1.2k

点赞数 24

分类专栏： SQL Server 文章标签：数据库 sql

本文链接：https://blog.csdn.net/weixin_39604653/article/details/147621152

版权

SQL Server 专栏收录该内容

41 篇文章

订阅专栏

在 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;