【SQL Sever】3. 用户管理 / 权限管理

1. 创建登录名/用户/角色

在SQL Server中，创建用户通常涉及几个步骤。
首先，你需要创建一个登录名，然后你可以基于这个登录名在数据库中创建一个用户。
以下是如何做到这一点的步骤和相应的SQL语句：

1. 创建登录名
   首先，你需要创建一个登录名。登录名允许用户连接到SQL Server实例。

USE [master];  
CREATE LOGIN [YourLoginName] WITH PASSWORD=N'YourPassword';
1
2

在这里，[YourLoginName] 是你希望为登录名指定的名称，YourPassword 是登录名的密码。

2. 在数据库中创建用户
   一旦你有了登录名，你就可以在特定的数据库中基于这个登录名创建一个用户。

USE [YourDatabaseName];  
CREATE USER [YourUserName] FOR LOGIN [YourLoginName];
1
2

在这里，[YourDatabaseName] 是你希望在其中创建用户的数据库名称，[YourUserName] 是你希望为数据库用户指定的名称，而 [YourLoginName] 是你在第一步中创建的登录名。

3. 为用户分配角色和权限
   你可能还想为用户分配特定的数据库角色或权限。例如，你可以将用户添加到 db_datareader 角色，以允许他们读取数据库中的数据：

USE [YourDatabaseName];  
EXEC sp_addrolemember N'db_datareader', N'YourUserName';
1
2

或者，你可以直接为用户授予或拒绝特定的权限：

USE [YourDatabaseName];  
GRANT SELECT ON [YourSchemaName].[YourTableName] TO [YourUserName];  
1
2

– 或者

DENY UPDATE ON [YourSchemaName].[YourTableName] TO [YourUserName];
1

4. 创建角色并分配权限：

USE [YourDatabaseName];  
CREATE ROLE [YourRoleName];  
GRANT SELECT, INSERT, UPDATE ON SCHEMA::[dbo] TO [YourRoleName];
1
2
3

请注意，为了执行上述操作，你需要有足够的权限。通常，数据库管理员或具有适当权限的用户才能执行这些操作。

2. 登录名/用户/角色的区别

在 SQL Server 中，登录名（Login）、用户（User）和角色（Role）是安全模型中的关键组件，它们各自具有特定的功能和用途。下面是关于这些组件的简要说明：

登录名（Login）
登录名是用于连接到 SQL Server 实例的身份验证实体。它可以是 SQL Server 身份验证（使用用户名和密码）或 Windows 身份验证（使用 Windows 用户名和密码或组）的一部分。登录名确定了谁可以连接到 SQL Server，但并不直接确定在连接到特定数据库时可以执行哪些操作。

用户（User）
用户是与特定数据库相关联的安全主体。在 SQL Server 中，每个登录名在尝试连接到数据库时都需要一个相应的数据库用户。这个数据库用户可以是基于登录名的，也可以是基于其他安全主体的（例如其他数据库用户或 Windows 组）。用户决定了在特定数据库范围内可以执行哪些操作。

角色（Role）
角色是一组用户的集合，用于简化权限管理。通过将一组相关的权限分配给一个角色，然后将用户添加到该角色，您可以轻松地管理一组用户的权限，而不必单独为每个用户分配权限。SQL Server 提供了固定服务器角色（在服务器级别）和固定数据库角色（在数据库级别），但您也可以创建自定义的服务器角色和数据库角色来满足特定的需求。

关系和交互
登录名与用户的关系：通常，当您在 SQL Server 中创建一个登录名并希望该登录名能够访问某个数据库时，您需要在该数据库中为该登录名创建一个用户。这个用户与登录名相关联，并继承登录名的身份验证信息。
用户与角色的关系：用户可以被添加到一个或多个角色中。通过这样做，用户将继承角色所拥有的所有权限。这允许管理员通过管理角色来简化权限管理过程。
示例场景
假设您有一个名为 JohnDoe 的员工，您希望他能够连接到 SQL Server 实例并查询某个特定数据库中的数据。您可能会执行以下步骤：

在 SQL Server 实例上创建一个名为 JohnDoe 的登录名。
在目标数据库中创建一个与 JohnDoe 登录名相关联的用户。
将该用户添加到一个具有适当查询权限的数据库角色中（例如 db_datareader）。
这样，当 JohnDoe 使用其登录凭据连接到 SQL Server 并选择目标数据库时，他将能够执行该角色允许的所有操作（在本例中是查询数据）。

当我们创建登录名+密码后，就可以通过输入密码的方式，以该登录名登录数据库
当我们创建用户名后，想要访问数据库，需要在数据库下创建对应的用户名再赋予其数据库的权限，才能执行后续操作。

// 使用数据库
use scott;
// 创建用户名(在登录名login hacha下) 
create user hacha for login hacha;
// 赋予hacha 查权限
grant select on schema::[dbo] to hacha;
1
2
3
4
5
6

无法进行修改/增加等操作，因为没有权限
否则一直会报如下错误：

消息 15151，级别 16，状态 1，第 5 行
无法对 用户 'hacha' 执行 查找，因为它不存在，或者您没有所需的权限。
1
2

3. 查询当前用户的用户名

在SQL Server中，你可以使用CURRENT_USER或SYSTEM_USER函数来获取当前用户的用户名。
这些函数返回当前SQL Server会话的数据库用户名。

以下是查询当前用户名的SQL语句：

SELECT CURRENT_USER AS CurrentUserName;
1

或者

SELECT SYSTEM_USER AS CurrentUserName;
1

这两个函数在大多数情况下会返回相同的结果，但它们的语义略有不同：
CURRENT_USER返回当前执行上下文的数据库用户名。
如果在执行存储过程或触发器时更改了执行上下文（例如，使用EXECUTE AS），则CURRENT_USER可能会返回不同的值。
SYSTEM_USER返回与当前SQL Server会话关联的原始登录名。
它不会受到执行上下文更改的影响。
在大多数日常场景中，CURRENT_USER和SYSTEM_USER将返回相同的结果，因为它们都代表当前会话的用户。
然而，在涉及执行上下文更改的高级场景中，它们可能有所不同。

示例：
当您看到 CURRENT_USER 返回 dbo 而 SYSTEM_USER 返回 sa 时，这通常意味着：
您使用 sa 登录名登录到 SQL Server。
查询是在一个上下文中执行的，其中当前用户被映射为 dbo 用户。这可能是因为查询是从一个存储过程、触发器或使用了 EXECUTE AS 的其他上下文中执行的，或者是因为当前数据库的安全设置使得 sa 登录名被映射为 dbo 用户。
dbo 是一个特殊的数据库用户，通常拥有数据库中的大部分权限（尽管不是所有权限，特别是如果数据库启用了某些限制性的安全选项）。而 sa 是一个具有服务器范围内几乎所有权限的登录名。

如果你想要获取SQL Server登录名（而不是数据库用户名），你可以查询sys.dm_exec_sessions动态管理视图（DMV），结合sys.server_principals目录视图：

SELECT s.login_name   
FROM sys.dm_exec_sessions s   
WHERE s.session_id = @@SPID;
1
2
3

这里，@@SPID是一个系统函数，它返回当前会话的进程ID（session ID），sys.dm_exec_sessions包含有关当前服务器上所有会话的信息。

请注意，执行上述查询可能需要特定的权限，具体取决于你的SQL Server配置和角色成员资格。如果你没有足够的权限，你可能需要联系数据库管理员来获取所需的信息。

4. 给用户赋予数据库的权限

1. 创建或确认用户存在
2. 分配数据库权限
   一旦用户存在，你就可以给它分配数据库级别的权限了。这可以通过 GRANT 语句完成。

分配基本的数据操作权限（如 SELECT, INSERT, UPDATE, DELETE）：

USE [YourDatabaseName];  
GRANT SELECT, INSERT, UPDATE, DELETE ON SCHEMA::[dbo] TO [YourDatabaseUserName];
1
2

这将授予用户在 dbo 模式下对表进行基本的 CRUD 操作。
如果你想针对特定的表而不是整个模式进行授权，可以替换 SCHEMA::[dbo] 为具体的表名，例如 OBJECT::[dbo].[YourTableName]。

分配更高级别的权限（如 ALTER, EXECUTE）：

USE [YourDatabaseName];  
GRANT ALTER, EXECUTE ON SCHEMA::[dbo] TO [YourDatabaseUserName];
1
2

这将允许用户修改模式和执行存储过程或函数。

分配数据库级别的权限（如 BACKUP DATABASE, CREATE TABLE）：

USE [YourDatabaseName];  
GRANT BACKUP DATABASE, CREATE TABLE TO [YourDatabaseUserName];
1
2

这将允许用户备份数据库和创建新表。

3. 考虑使用角色来管理权限
   为了简化权限管理，你可以考虑使用数据库角色。
   你可以创建自定义角色，将权限分配给这些角色，然后将用户添加到这些角色中。
   这样，当你需要更改用户的权限时，只需更改角色的权限即可，而不必单独修改每个用户的权限。

创建角色并分配权限：

USE [YourDatabaseName];  
CREATE ROLE [YourRoleName];  
GRANT SELECT, INSERT, UPDATE ON SCHEMA::[dbo] TO [YourRoleName];
1
2
3

将用户添加到角色：

USE [YourDatabaseName];  
EXEC sp_addrolemember N'YourRoleName', N'YourDatabaseUserName';
1
2

注意事项：
确保在执行这些操作时具有足够的权限。通常需要是 db_owner、db_securityadmin 或具有类似权限的用户。
仔细考虑授予的权限级别，避免授予不必要的权限，以减少潜在的安全风险。
在生产环境中更改权限之前，最好先在测试环境中验证更改的影响。

5. 查看当前SQL Sever登录名

在 SQL Server 中，要查看当前实例中存在的所有用户（登录名），你可以查询 sys.server_principals 系统视图。
这个视图包含了 SQL Server 实例中所有服务器级别的安全主体，包括登录名、服务器角色和应用程序角色。

以下是一个简单的 SQL 查询，用于列出 SQL Server 实例中的所有登录名（用户）：

USE master;  
GO  
  
SELECT name, type_desc   
FROM sys.server_principals   
WHERE type_desc IN ('SQL_LOGIN', 'WINDOWS_LOGIN', 'WINDOWS_GROUP', 'CERTIFICATE', 'ASYMMETRIC_KEY', 'EXTERNAL_LOGIN');
1
2
3
4
5
6

这个查询会返回所有类型的登录名，包括 SQL 登录名、Windows 登录名、Windows 组、证书登录名、非对称密钥登录名和外部登录名。如果你只对 SQL 登录名和 Windows 登录名感兴趣，可以进一步过滤 type_desc 的值。
如果你只想看 SQL 登录名和 Windows 登录名，可以使用以下查询：

USE master;  
GO  
  
SELECT name, type_desc   
FROM sys.server_principals   
WHERE type_desc IN ('SQL_LOGIN', 'WINDOWS_LOGIN');
1
2
3
4
5
6

这个查询将只返回 SQL 登录名和 Windows 登录名。
请注意，执行这些查询通常需要具有足够的权限。如果你没有足够的权限，可能无法看到所有的登录名。在这种情况下，你可能需要联系你的数据库管理员来获取所需的信息。
此外，sys.server_principals 视图中的 type_desc 列描述了主体的类型。
例如，SQL_LOGIN 表示一个 SQL Server 登录名，而 WINDOWS_LOGIN 表示一个 Windows 登录名。
通过查看这个列，你可以了解每个主体的类型。

6. SQLSever中有root账号吗？

在 SQL Server 中，并没有一个名为 “root” 的默认账号。
在 Unix 或 Linux 系统中，“root” 是超级用户账号，具有对系统的完全控制权。
但在 Windows 和 SQL Server 的环境中，概念略有不同。
在 SQL Server 中，拥有最高权限的账号通常是 sa（系统管理员）账号。
这个账号在 SQL Server 安装过程中创建，并具有对 SQL Server 实例的完全控制权。
与 Unix/Linux 中的 “root” 类似，sa 账号应该谨慎使用，并且其密码应该妥善保管，以防止未经授权的访问。
除了 sa 账号外，SQL Server 还支持 Windows 身份验证模式，其中 Windows 域或本地组的成员可以作为 SQL Server 的登录名。在这种情况下，具有适当 Windows 权限的用户或组可以拥有对 SQL Server 的访问权限。
如果你正在寻找具有高级权限的账号来管理 SQL Server，你应该考虑使用 sa 账号或具有适当权限的 Windows 用户或组。但是，请务必注意安全和权限管理的最佳实践，以避免潜在的安全风险。

7. 报错：无法对 用户 ‘hx’ 执行 查找，因为它不存在，或者您没有所需的权限。

使用 T-SQL 查询：你可以使用以下 T-SQL 查询来检查 ‘hx’ 用户是否存在：

sql
USE master;  
GO  
  
SELECT name, type_desc   
FROM sys.server_principals   
WHERE name = 'hx';
1
2
3
4
5
6
7

如果查询返回了结果，那么 ‘hx’ 用户确实存在。如果查询没有返回任何结果，那么用户不存在，你需要创建它。

8. 不存在多行注释

在 SQL Server 中，没有像一些其他编程语言（如 C# 或 Java）那样的多行注释符号（例如 / … /）。
然而，你可以使用单行注释符号 – 来达到多行注释的效果，只需要在每一行前面都加上 – 即可。
例如：

-- 这是第一行注释  
-- 这是第二行注释  
-- 这是第三行注释  
  
SELECT * FROM YourTableName; -- 这仍然是一个单行注释
1
2
3
4
5

如果你经常需要写多行注释，并且觉得上面的方法不够优雅或不够方便，你可以考虑使用存储过程或脚本来管理你的 SQL 代码，并在其中使用外部文本编辑器或 IDE 的多行注释功能。然后，你可以将编辑好的 SQL 代码复制到 SQL Server Management Studio (SSMS) 或其他查询工具中执行。
此外，一些第三方的 SQL 工具或插件可能提供了在 SQL Server 中使用多行注释的功能，但这不是 SQL Server 本身的功能。
如果你确实需要在 SQL Server 中处理大量的多行注释，并且想要更自动化的方式，你可能需要考虑使用版本控制系统（如 Git）和源代码编辑器（如 Visual Studio Code、SSMS 的插件等），它们通常都支持多行注释的添加和移除。

9. 报错：无法对 用户 ‘hx’ 执行 删除，因为它不存在，或者您没有所需的权限。

SELECT name, type_desc   
FROM sys.server_principals   
WHERE type_desc IN ('SQL_LOGIN', 'WINDOWS_LOGIN');

drop user hx;
1
2
3
4
5

消息 15151，级别 16，状态 1，第 2 行
无法对 用户 ‘hx’ 执行 删除，因为它不存在，或者您没有所需的权限。

根据你提供的信息，你试图删除一个名为 ‘hx’ 的用户，但是收到了一个错误消息，提示 ‘hx’ 不存在或你没有足够的权限。

首先，drop user 语句在 SQL Server 中用于从特定数据库中删除用户，而不是从服务器级别删除登录名。
如果你尝试使用 drop user 来删除一个登录名，你会收到一个错误，因为登录名和用户是两个不同的概念。
登录名是在服务器级别定义的，而用户则是在数据库级别定义的，并与登录名关联。

在你之前的查询中，你使用 sys.server_principals 视图列出了所有的登录名，包括 SQL 登录名、Windows 登录名和 Windows 组。
结果显示 ‘hx’ 是一个 SQL_LOGIN 类型的登录名。
要删除一个登录名，你应该使用 DROP LOGIN 语句，而不是 DROP USER。
所以，你应该执行以下命令来删除 ‘hx’ 登录名：

USE master;  
GO  
  
DROP LOGIN hx;  
GO
1
2
3
4
5

请注意，执行这个操作需要你有足够的权限。通常，只有 sysadmin 固定服务器角色的成员或具有相应权限的用户才能删除登录名。

如果你只是想从特定数据库中删除一个用户（假设该用户与 ‘hx’ 登录名关联），你需要首先确保你连接到了正确的数据库，然后执行类似以下的命令：

USE YourDatabaseName;  
GO  
  
DROP USER [hx];  
GO
1
2
3
4
5

请替换 YourDatabaseName 为你想要从中删除用户的数据库名称。

在尝试删除任何对象之前，请始终确保你了解这些操作的后果，并且已经做好了适当的备份和恢复计划。