SQL Server：用户权限

创建 & 删除

1. 创建用户命令整理

创建 admin2 用户

-- 在 master 数据库创建登录名
USE master;
BEGINCREATE LOGIN [admin2] WITH PASSWORD=N'Cljslrl0620!', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=ON;
END;-- 在 db03 数据库创建用户并添加到相应角色
USE [db03];
IF NOT EXISTS (SELECT 1 FROM sysusers WITH(NOLOCK) WHERE name = 'admin2')
BEGINCREATE USER [admin2] FOR LOGIN [admin2];
END;
ALTER ROLE [db_datareader] ADD MEMBER [admin2];
ALTER ROLE [db_datawriter] ADD MEMBER [admin2];
ALTER ROLE [db_ddladmin] ADD MEMBER [admin2];

创建 admin_super 用户

-- 在 master 数据库创建登录名并添加到服务器角色
USE master;
BEGINCREATE LOGIN [admin_super] WITH PASSWORD=N'Cljslrl0620!', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=ON;
END;
ALTER SERVER ROLE [processadmin] ADD MEMBER [admin_super];
ALTER SERVER ROLE [setupadmin] ADD MEMBER [admin_super];-- 在 msdb 数据库创建用户并添加到相应角色
USE msdb;
IF NOT EXISTS (SELECT 1 FROM sysusers WITH(NOLOCK) WHERE name = 'admin_super')
BEGINCREATE USER [admin_super] FOR LOGIN [admin_super];
END;
ALTER ROLE [SQLAgentOperatorRole] ADD MEMBER [admin_super];
ALTER ROLE [SQLAgentReaderRole] ADD MEMBER [admin_super];
ALTER ROLE [SQLAgentUserRole] ADD MEMBER [admin_super];
ALTER ROLE [db_datareader] ADD MEMBER [admin_super];
IF EXISTS(SELECT 1 FROM sys.procedures WITH(NOLOCK) WHERE name = 'rds_cdc_enable_db')
BEGINGRANT EXECUTE ON [dbo].[rds_cdc_enable_db] TO [admin_super];GRANT EXECUTE ON [dbo].[rds_cdc_disable_db] TO [admin_super];
END;-- 在 db03 数据库创建用户并添加到相应角色，拒绝备份权限
USE [db03];
IF NOT EXISTS (SELECT 1 FROM sysusers WITH(NOLOCK) WHERE name = 'admin_super')
BEGINCREATE USER [admin_super] FOR LOGIN [admin_super];
END;
ALTER ROLE db_owner ADD MEMBER [admin_super];
DENY BACKUP LOG TO [admin_super];
DENY BACKUP DATABASE TO [admin_super];-- 在 db02 数据库创建用户并添加到相应角色，拒绝备份权限
USE [db02];
IF NOT EXISTS (SELECT 1 FROM sysusers WITH(NOLOCK) WHERE name = 'admin_super')
BEGINCREATE USER [admin_super] FOR LOGIN [admin_super];
END;
ALTER ROLE db_owner ADD MEMBER [admin_super];
DENY BACKUP LOG TO [admin_super];
DENY BACKUP DATABASE TO [admin_super];-- 在 db01 数据库创建用户并添加到相应角色，拒绝备份权限
USE [db01];
IF NOT EXISTS (SELECT 1 FROM sysusers WITH(NOLOCK) WHERE name = 'admin_super')
BEGINCREATE USER [admin_super] FOR LOGIN [admin_super];
END;
ALTER ROLE db_owner ADD MEMBER [admin_super];
DENY BACKUP LOG TO [admin_super];
DENY BACKUP DATABASE TO [admin_super];

2. 删除用户命令

删除 admin2 用户

-- 删除登录名
USE master;
DROP LOGIN [admin2];-- 删除 db03 数据库中的用户
USE [db03];
DROP USER IF EXISTS [admin2];

删除 admin_super 用户

-- 删除登录名
USE master;
DROP LOGIN [admin_super];-- 删除 msdb 数据库中的用户
USE msdb;
DROP USER IF EXISTS [admin_super];-- 删除 db03 数据库中的用户
USE [db03];
DROP USER IF EXISTS [admin_super];-- 删除 db02 数据库中的用户
USE [db02];
DROP USER IF EXISTS [admin_super];-- 删除 db01 数据库中的用户
USE [db01];
DROP USER IF EXISTS [admin_super];

3. 创建时权限的区别

admin2 用户权限

• 数据库范围：仅在 db03 数据库有相关权限。
• 角色权限：被添加到 db_datareader（可读取数据库中所有用户表中的数据）、db_datawriter（可在所有用户表中添加、删除或更改数据）和 db_ddladmin（可以在数据库中运行任何数据定义语言 (DDL) 命令）角色，拥有数据库层面的数据读写和 DDL 操作权限。

admin_super 用户权限

• 服务器角色权限：被添加到 processadmin（可以终止在 SQL Server 实例中运行的进程）和 setupadmin（可以添加和删除链接服务器）服务器角色，拥有服务器层面的进程管理和链接服务器管理权限。
• 数据库范围：在 msdb、db03、db02 和 db01 数据库都有相关权限。
  • msdb 数据库：被添加到 SQLAgentOperatorRole、SQLAgentReaderRole、SQLAgentUserRole 和 db_datareader 角色，同时如果存在 rds_cdc_enable_db 和 rds_cdc_disable_db 存储过程，还拥有对这两个存储过程的执行权限。
  • db03、db02、db01 数据库：被添加到 db_owner 角色（拥有数据库的完全控制权），但被拒绝了 BACKUP LOG 和 BACKUP DATABASE 权限，即不能进行数据库日志备份和数据库完整备份操作。

查看

在 SQL Server Management Studio (SSMS) 中查看用户权限，可通过对象资源管理器以图形化界面查看，也能使用系统视图通过编写查询语句来查看。以下为你详细介绍这两种方法：

方法一：使用对象资源管理器（图形化界面）

查看服务器级别权限

1. 打开 SSMS 并连接到目标 SQL Server 实例。
2. 在对象资源管理器中，展开“安全性”节点，然后展开“登录名”节点。
3. 右键单击要查看权限的用户登录名，选择“属性”。
4. 在弹出的“登录属性”窗口中，选择“服务器角色”页签，可以查看该用户所属的服务器角色，不同的服务器角色拥有不同的服务器级别权限。
5. 选择“用户映射”页签，这里可以看到该用户映射到的数据库以及在每个数据库中的角色成员身份。
6. 选择“安全对象”页签，点击“搜索”按钮，在弹出的“添加对象”对话框中，选择要查看权限的对象类型（如数据库、表等），然后选择具体的对象。点击“确定”后，下方会显示该用户对所选对象的权限情况。

查看数据库级别权限

1. 在对象资源管理器中，展开要查看的数据库节点，然后展开“安全性”节点，再展开“用户”节点。
2. 右键单击要查看权限的用户，选择“属性”。
3. 在弹出的“数据库用户 - [用户名]”窗口中，选择“成员身份”页签，可以查看该用户所属的数据库角色。
4. 选择“安全对象”页签，操作方法与查看服务器级别权限时类似，点击“搜索”按钮选择要查看的对象，然后查看该用户对这些对象的权限。

方法二：使用系统视图查询

查看服务器级别权限

SELECT pr.principal_id,pr.name,pr.type_desc,pe.state_desc,pe.permission_name
FROM sys.server_permissions pe
JOIN sys.server_principals pr ON pe.grantee_principal_id = pr.principal_id
WHERE pr.name = 'your_login_name';

将 your_login_name 替换为要查看权限的用户登录名，执行上述查询后，即可查看该用户的服务器级别权限。

查看数据库级别权限

SELECT dp.class_desc,OBJECT_NAME(dp.major_id) AS object_name,dp.permission_name,grantee_principal_name = grp.name,grantor_principal_name = gran.name
FROM sys.database_permissions dp
JOIN sys.database_principals grp ON dp.grantee_principal_id = grp.principal_id
JOIN sys.database_principals gran ON dp.grantor_principal_id = gran.principal_id
WHERE grp.name = 'your_database_user_name';

将 your_database_user_name 替换为要查看权限的数据库用户名，执行该查询后，就能看到该用户的数据库级别权限。

服务器角色 — 预定义的权限集合

在MSSQL（Microsoft SQL Server）中，服务器角色是一组预定义的权限集合，用于简化对登录名（服务器级主体）权限的管理 。以下是常见服务器角色及其说明：

1. bulkadmin：
   • 权限：允许执行 BULK INSERT 语句，能将数据从文件高效导入到SQL Server表中，常用于大数据量的快速导入场景，比如从CSV文件导入数据到数据库表。
   • 应用场景：数据仓库构建过程中，需要批量导入外部数据文件时，可将相关登录名加入此角色。
2. dbcreator：
   • 权限：可创建、更改、删除和还原数据库。拥有对数据库创建、修改和删除等生命周期管理的权限。
   • 应用场景：数据库开发人员或运维人员，在测试环境、开发环境中频繁创建和删除测试数据库时，可赋予此角色。
3. diskadmin：
   • 权限：用于管理磁盘文件，包括添加或删除数据库文件（数据文件和日志文件），以及管理数据库的文件组等与磁盘存储相关的操作。
   • 应用场景：当数据库需要扩容，添加新的数据文件，或者整理文件组布局时，需要该角色权限。
4. processadmin：
   • 权限：可以终止SQL Server实例中运行的进程。比如，当某个查询或进程长时间占用资源，影响数据库性能时，可使用此权限来终止该进程。
   • 应用场景：数据库管理员在处理性能问题，排查异常占用资源的进程时会用到。
5. public：
   • 权限：每个SQL Server登录名默认都属于 public 角色，它是一个特殊的、基础的角色。 public 角色包含了一些最基本的权限，如查看数据库中的某些元数据等，但权限非常有限。
   • 应用场景：作为默认的、兜底的角色，所有登录名都自动拥有 public 角色赋予的权限，不过实际中往往还需赋予其他更具体的角色权限来满足业务需求。
6. securityadmin：
   • 权限：可管理登录名及其属性，包括创建、修改和删除登录名，还能管理服务器角色成员，以及管理权限的授予、拒绝和撤销等安全相关操作。
   • 应用场景：在企业中，负责用户账号和权限管理的人员，需要具备此角色权限来进行用户认证和权限分配工作。
7. serveradmin：
   • 权限：可以更改服务器范围的配置选项，关闭SQL Server实例等操作。例如，调整服务器的内存分配、配置网络相关设置等。
   • 应用场景：高级数据库管理员在对服务器进行整体配置优化、停机维护等操作时需要此权限。
8. setupadmin：
   • 权限：可添加和删除链接服务器，以及执行某些系统存储过程来配置服务器相关的设置，用于管理服务器之间的连接和通信。
   • 应用场景：当企业有多台数据库服务器，需要建立链接服务器来实现跨服务器的数据查询和操作时，需要该角色权限。
9. sysadmin：
   • 权限：拥有对SQL Server实例的完全控制权限，几乎可以执行所有操作，包括创建、修改和删除数据库，管理登录名、服务器角色，执行任何T - SQL语句等。
   • 应用场景：一般仅授予数据库系统的超级管理员，在实际生产环境中，为了安全考虑，通常会尽量减少拥有 sysadmin 角色的人员数量。