SQL Server 新建用户并赋予数据库权限图文教程
一、准备工作
1.1 启动SQL Server Management Studio (SSMS)
1.2 连接到SQL Server实例
- 输入服务器名称
- 选择身份验证方式（Windows/SQL Server）
- 点击"连接"
二、新建登录账号（两种方式）
方式一：使用SSMS图形界面
步骤1：打开"安全性"文件夹
步骤2：右键"登录名" → "新建登录名"
步骤3：配置登录信息
-- 示例配置：
-- 登录名: newuser
-- 身份验证: SQL Server身份验证
-- 密码: YourPassword123
-- 取消勾选"强制实施密码策略"（根据需求）
步骤4：设置默认数据库
方式二：使用T-SQL命令
-- 新建SQL Server登录账号
USE [master]
GO
CREATE LOGIN [newuser]
WITH PASSWORD = N'YourPassword123',
DEFAULT_DATABASE = [YourDatabase],
CHECK_EXPIRATION = OFF,
CHECK_POLICY = OFF
GO
三、在目标数据库中创建用户
方式一：使用SSMS图形界面
步骤1：展开目标数据库 → 安全性 → 右键"用户" → "新建用户"
步骤2：配置用户信息
-- 示例配置：
-- 用户名: newuser_db
-- 登录名: newuser (选择刚才创建的登录名)
-- 默认架构: dbo
方式二：使用T-SQL命令
-- 切换到目标数据库
USE [YourDatabase]
GO
-- 创建数据库用户并关联登录账号
CREATE USER [newuser_db] FOR LOGIN [newuser]
WITH DEFAULT_SCHEMA = [dbo]
GO
四、授予数据库权限（三种方法）
方法1：通过SSMS授予角色权限
步骤1：打开用户属性 → 选择"成员身份"页签
步骤2：选择数据库角色
-- 常用角色说明：
-- db_owner: 数据库所有者，拥有全部权限
-- db_datareader: 可以读取所有表数据
-- db_datawriter: 可以插入、更新、删除所有表数据
-- db_ddladmin: 可以执行DDL操作（创建、修改表等）
-- public: 默认角色，所有用户都有
方法2：通过SSMS授予具体权限
步骤1：右键数据库 → 属性 → 权限
步骤2：选择用户 → 勾选具体权限
方法3：使用T-SQL命令授权
-- 授予数据库角色成员身份
USE [YourDatabase]
GO
-- 加入db_datareader角色（只读）
EXEC sp_addrolemember 'db_datareader', 'newuser_db'
GO
-- 加入db_datawriter角色（增删改）
EXEC sp_addrolemember 'db_datawriter', 'newuser_db'
GO
-- 授予具体权限
USE [YourDatabase]
GO
-- 授予SELECT权限
GRANT SELECT ON [dbo].[TableName] TO [newuser_db]
GO
-- 授予EXECUTE权限（存储过程）
GRANT EXECUTE ON [dbo].[StoredProcedure] TO [newuser_db]
GO
-- 授予所有权限
GRANT ALL ON [dbo].[TableName] TO [newuser_db]
GO
五、验证权限设置
5.1 使用新建用户登录测试
步骤1：断开当前连接
步骤2：使用新建用户登录
5.2 执行权限测试
-- 测试只读权限
SELECT * FROM [dbo].[YourTable] -- 应该成功
-- 测试写入权限（如果授予了）
INSERT INTO [dbo].[YourTable] (Column1) VALUES ('Test') -- 根据权限可能成功或失败
-- 测试无权限的操作
DROP TABLE [dbo].[YourTable] -- 应该失败
5.3 查看用户权限
-- 查看用户拥有的权限
USE [YourDatabase]
GO
-- 查看用户角色成员身份
SELECT
dp.name AS UserName,
roles.name AS RoleName
FROM sys.database_role_members rm
JOIN sys.database_principals dp ON rm.member_principal_id = dp.principal_id
JOIN sys.database_principals roles ON rm.role_principal_id = roles.principal_id
WHERE dp.name = 'newuser_db'
GO
-- 查看具体权限
SELECT
USER_NAME(grantee_principal_id) AS UserName,
permission_name,
state_desc,
OBJECT_NAME(major_id) AS ObjectName
FROM sys.database_permissions
WHERE USER_NAME(grantee_principal_id) = 'newuser_db'
GO
六、权限管理脚本示例
6.1 完整权限设置脚本
-- ============================================
-- SQL Server用户权限设置完整脚本
-- ============================================
-- 1. 创建登录账号
USE [master]
GO
CREATE LOGIN [sales_user]
WITH PASSWORD = N'Sales@2024',
DEFAULT_DATABASE = [SalesDB],
CHECK_EXPIRATION = OFF,
CHECK_POLICY = OFF
GO
-- 2. 创建数据库用户
USE [SalesDB]
GO
CREATE USER [sales_user_db] FOR LOGIN [sales_user]
WITH DEFAULT_SCHEMA = [sales]
GO
-- 3. 授予角色权限
EXEC sp_addrolemember 'db_datareader', 'sales_user_db'
EXEC sp_addrolemember 'db_datawriter', 'sales_user_db'
GO
-- 4. 授予架构权限
GRANT SELECT, INSERT, UPDATE, DELETE ON SCHEMA::[sales] TO [sales_user_db]
GO
-- 5. 授予具体对象权限（可选）
GRANT EXECUTE ON [dbo].[usp_GetSalesReport] TO [sales_user_db]
GO
-- 6. 拒绝特定权限（如果需要）
DENY DELETE ON [dbo].[AuditLog] TO [sales_user_db]
GO
6.2 权限撤销脚本
-- 移除角色成员身份
EXEC sp_droprolemember 'db_datawriter', 'sales_user_db'
GO
-- 撤销具体权限
REVOKE DELETE ON [dbo].[YourTable] FROM [sales_user_db]
GO
-- 删除数据库用户
DROP USER [sales_user_db]
GO
-- 删除登录账号（在master数据库中）
USE [master]
GO
DROP LOGIN [sales_user]
GO
七、最佳实践和注意事项
7.1 安全建议
最小权限原则：只授予必要的最小权限
使用角色管理：通过数据库角色批量管理权限
定期审计：定期检查用户权限
密码策略：启用强密码策略
使用Windows身份验证：如果环境允许，优先使用Windows身份验证
7.2 常见问题解决
问题1：用户无法登录
-- 检查登录账号状态
SELECT name, is_disabled FROM sys.server_principals WHERE name = 'newuser'
-- 启用被禁用的账号
ALTER LOGIN [newuser] ENABLE
问题2：用户无数据库访问权限
-- 检查数据库用户映射
USE [YourDatabase]
SELECT name FROM sys.database_principals WHERE name = 'newuser_db'
问题3：权限不生效
-- 清除权限缓存
DBCC FREEPROCCACHE
GO
八、可视化权限管理工具推荐
SSMS内置工具：最常用，功能全面
ApexSQL Manage：第三方工具，提供更友好的界面
dbForge Studio：商业化工具，提供高级功能
总结
通过本教程，您已经学会了：
- ✅ 创建SQL Server登录账号
- ✅ 在目标数据库中创建用户
- ✅ 授予数据库角色权限
- ✅ 授予具体对象权限
- ✅ 验证权限设置
- ✅ 使用T-SQL脚本管理权限
按照"先创建登录账号 → 再创建数据库用户 → 最后分配权限"的流程操作，可以有效管理SQL Server用户权限。
