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 安全建议

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

八、可视化权限管理工具推荐

总结

通过本教程，您已经学会了：

• ✅ 创建SQL Server登录账号
• ✅ 在目标数据库中创建用户
• ✅ 授予数据库角色权限
• ✅ 授予具体对象权限
• ✅ 验证权限设置
• ✅ 使用T-SQL脚本管理权限

按照"先创建登录账号 → 再创建数据库用户 → 最后分配权限"的流程操作，可以有效管理SQL Server用户权限。