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

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

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

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

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

复制代码

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

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

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

复制代码

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

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

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

复制代码

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

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

复制代码

– 或者

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

复制代码

• 创建脚色并分配权限：
  

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

复制代码

请注意，为了实行上述操作，你需要有充足的权限。通常，数据库管理员或具有适当权限的用户才能实行这些操作。
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 并选择目的数据库时，他将可以或许实行该脚色允许的所有操作（在本例中是查询数据）。
当我们创建登录名+暗码后，就可以通过输入暗码的方式，以该登录名登录数据库
当我们创建用户名后，想要访问数据库，需要在数据库下创建对应的用户名再赋予其数据库的权限，才能实行后续操作。

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

复制代码

无法进行修改/增加等操作，由于没有权限
否则不停会报如下错误：

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

复制代码

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

在SQL Server中，你可以利用CURRENT_USER或SYSTEM_USER函数来获取当前用户的用户名。
这些函数返回当前SQL Server会话的数据库用户名。
以下是查询当前用户名的SQL语句：

1. SELECT CURRENT_USER AS CurrentUserName;

复制代码

或者

1. SELECT SYSTEM_USER AS CurrentUserName;

复制代码

这两个函数在大多数情况下会返回雷同的效果，但它们的语义略有不同：
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目次视图：

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

复制代码

这里，@@SPID是一个体系函数，它返回当前会话的进程ID（session ID），sys.dm_exec_sessions包罗有关当前服务器上所有会话的信息。
请注意，实行上述查询可能需要特定的权限，详细取决于你的SQL Server设置和脚色成员资格。假如你没有充足的权限，你可能需要联系数据库管理员来获取所需的信息。
4. 给用户赋予数据库的权限

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

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

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

复制代码

这将授予用户在 dbo 模式下对表进行基本的 CRUD 操作。
假如你想针对特定的表而不是整个模式进行授权，可以替换 SCHEMA::[dbo] 为详细的表名，例如 OBJECT::[dbo].[YourTableName]。
分配更高级别的权限（如 ALTER, EXECUTE）：

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

复制代码

这将允许用户修改模式和实行存储过程或函数。
分配数据库级别的权限（如 BACKUP DATABASE, CREATE TABLE）：

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

复制代码

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

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

创建脚色并分配权限：

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

复制代码

将用户添加到脚色：

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

复制代码

注意事项：
确保在实行这些操作时具有充足的权限。通常需要是 db_owner、db_securityadmin 或具有类似权限的用户。
细致思量授予的权限级别，避免授予不必要的权限，以减少潜伏的安全风险。
在生产情况中更改权限之前，最好先在测试情况中验证更改的影响。
5. 检察当前SQL Sever登录名

在 SQL Server 中，要检察当前实例中存在的所有效户（登录名），你可以查询 sys.server_principals 体系视图。
这个视图包罗了 SQL Server 实例中所有服务器级别的安全主体，包括登录名、服务器脚色和应用程序脚色。
以下是一个简朴的 SQL 查询，用于列出 SQL Server 实例中的所有登录名（用户）：

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

复制代码

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

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

复制代码

这个查询将只返回 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’ 用户是否存在：

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

复制代码

假如查询返回了效果，那么 ‘hx’ 用户确实存在。假如查询没有返回任何效果，那么用户不存在，你需要创建它。
8. 不存在多行表明

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

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

复制代码

假如你常常需要写多行表明，而且觉得上面的方法不够优雅或不够方便，你可以思量利用存储过程或脚本来管理你的 SQL 代码，并在其中利用外部文本编辑器或 IDE 的多行表明功能。然后，你可以将编辑好的 SQL 代码复制到 SQL Server Management Studio (SSMS) 或其他查询工具中实行。
此外，一些第三方的 SQL 工具或插件可能提供了在 SQL Server 中利用多行表明的功能，但这不是 SQL Server 自己的功能。
假如你确实需要在 SQL Server 中处置惩罚大量的多行表明，而且想要更主动化的方式，你可能需要思量利用版本控制体系（如 Git）和源代码编辑器（如 Visual Studio Code、SSMS 的插件等），它们通常都支持多行表明的添加和移除。
9. 报错：无法对 用户 ‘hx’ 实行 删除，由于它不存在，或者您没有所需的权限。

1. SELECT name, type_desc   
2. FROM sys.server_principals   
3. WHERE type_desc IN ('SQL_LOGIN', 'WINDOWS_LOGIN');
5. drop user hx;

复制代码

消息 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’ 登录名：

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

复制代码

请注意，实行这个操作需要你有充足的权限。通常，只有 sysadmin 固定服务器脚色的成员或具有相应权限的用户才能删除登录名。
假如你只是想从特定命据库中删除一个用户（假设该用户与 ‘hx’ 登录名关联），你需要首先确保你连接到了正确的数据库，然后实行类似以下的命令：

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

复制代码

请替换 YourDatabaseName 为你想要从中删除用户的数据库名称。
在尝试删除任何对象之前，请始终确保你了解这些操作的结果，而且已经做好了适当的备份和规复计划。

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。