1. 創(chuàng)建登錄名/用戶/角色

在SQL Server中，創(chuàng)建用戶通常涉及幾個(gè)步驟。

首先，你需要?jiǎng)?chuàng)建一個(gè)登錄名，然后你可以基于這個(gè)登錄名在數(shù)據(jù)庫中創(chuàng)建一個(gè)用戶。

以下是如何做到這一點(diǎn)的步驟和相應(yīng)的SQL語句：

• 創(chuàng)建登錄名
  首先，你需要?jiǎng)?chuàng)建一個(gè)登錄名。登錄名允許用戶連接到SQL Server實(shí)例。

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

在這里，[YourLoginName] 是你希望為登錄名指定的名稱，YourPassword 是登錄名的密碼。

• 在數(shù)據(jù)庫中創(chuàng)建用戶
  一旦你有了登錄名，你就可以在特定的數(shù)據(jù)庫中基于這個(gè)登錄名創(chuàng)建一個(gè)用戶。

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

在這里，[YourDatabaseName] 是你希望在其中創(chuàng)建用戶的數(shù)據(jù)庫名稱，[YourUserName] 是你希望為數(shù)據(jù)庫用戶指定的名稱，而 [YourLoginName] 是你在第一步中創(chuàng)建的登錄名。

• 為用戶分配角色和權(quán)限
  你可能還想為用戶分配特定的數(shù)據(jù)庫角色或權(quán)限。例如，你可以將用戶添加到 db_datareader 角色，以允許他們讀取數(shù)據(jù)庫中的數(shù)據(jù)：

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

或者，你可以直接為用戶授予或拒絕特定的權(quán)限：

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

– 或者

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

• 創(chuàng)建角色并分配權(quán)限：

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

請(qǐng)注意，為了執(zhí)行上述操作，你需要有足夠的權(quán)限。通常，數(shù)據(jù)庫管理員或具有適當(dāng)權(quán)限的用戶才能執(zhí)行這些操作。

2. 登錄名/用戶/角色的區(qū)別

在 SQL Server 中，登錄名（Login）、用戶（User）和角色（Role）是安全模型中的關(guān)鍵組件，它們各自具有特定的功能和用途。下面是關(guān)于這些組件的簡(jiǎn)要說明：

登錄名（Login）登錄名是用于連接到 SQL Server 實(shí)例的身份驗(yàn)證實(shí)體。它可以是 SQL Server 身份驗(yàn)證（使用用戶名和密碼）或 Windows 身份驗(yàn)證（使用 Windows 用戶名和密碼或組）的一部分。登錄名確定了誰可以連接到 SQL Server，但并不直接確定在連接到特定數(shù)據(jù)庫時(shí)可以執(zhí)行哪些操作。

用戶（User）用戶是與特定數(shù)據(jù)庫相關(guān)聯(lián)的安全主體。在 SQL Server 中，每個(gè)登錄名在嘗試連接到數(shù)據(jù)庫時(shí)都需要一個(gè)相應(yīng)的數(shù)據(jù)庫用戶。這個(gè)數(shù)據(jù)庫用戶可以是基于登錄名的，也可以是基于其他安全主體的（例如其他數(shù)據(jù)庫用戶或 Windows 組）。用戶決定了在特定數(shù)據(jù)庫范圍內(nèi)可以執(zhí)行哪些操作。

角色（Role）角色是一組用戶的集合，用于簡(jiǎn)化權(quán)限管理。通過將一組相關(guān)的權(quán)限分配給一個(gè)角色，然后將用戶添加到該角色，您可以輕松地管理一組用戶的權(quán)限，而不必單獨(dú)為每個(gè)用戶分配權(quán)限。SQL Server 提供了固定服務(wù)器角色（在服務(wù)器級(jí)別）和固定數(shù)據(jù)庫角色（在數(shù)據(jù)庫級(jí)別），但您也可以創(chuàng)建自定義的服務(wù)器角色和數(shù)據(jù)庫角色來滿足特定的需求。

關(guān)系和交互登錄名與用戶的關(guān)系：通常，當(dāng)您在 SQL Server 中創(chuàng)建一個(gè)登錄名并希望該登錄名能夠訪問某個(gè)數(shù)據(jù)庫時(shí)，您需要在該數(shù)據(jù)庫中為該登錄名創(chuàng)建一個(gè)用戶。這個(gè)用戶與登錄名相關(guān)聯(lián)，并繼承登錄名的身份驗(yàn)證信息。
用戶與角色的關(guān)系：用戶可以被添加到一個(gè)或多個(gè)角色中。通過這樣做，用戶將繼承角色所擁有的所有權(quán)限。這允許管理員通過管理角色來簡(jiǎn)化權(quán)限管理過程。

示例場(chǎng)景假設(shè)您有一個(gè)名為 JohnDoe 的員工，您希望他能夠連接到 SQL Server 實(shí)例并查詢某個(gè)特定數(shù)據(jù)庫中的數(shù)據(jù)。您可能會(huì)執(zhí)行以下步驟：

在 SQL Server 實(shí)例上創(chuàng)建一個(gè)名為 JohnDoe 的登錄名。

在目標(biāo)數(shù)據(jù)庫中創(chuàng)建一個(gè)與 JohnDoe 登錄名相關(guān)聯(lián)的用戶。

將該用戶添加到一個(gè)具有適當(dāng)查詢權(quán)限的數(shù)據(jù)庫角色中（例如 db_datareader）。

這樣，當(dāng) JohnDoe 使用其登錄憑據(jù)連接到 SQL Server 并選擇目標(biāo)數(shù)據(jù)庫時(shí)，他將能夠執(zhí)行該角色允許的所有操作（在本例中是查詢數(shù)據(jù)）。

當(dāng)我們創(chuàng)建登錄名+密碼后，就可以通過輸入密碼的方式，以該登錄名登錄數(shù)據(jù)庫
當(dāng)我們創(chuàng)建用戶名后，想要訪問數(shù)據(jù)庫，需要在數(shù)據(jù)庫下創(chuàng)建對(duì)應(yīng)的用戶名再賦予其數(shù)據(jù)庫的權(quán)限，才能執(zhí)行后續(xù)操作。

// 使用數(shù)據(jù)庫
use scott;
// 創(chuàng)建用戶名(在登錄名login hacha下) 
create user hacha for login hacha;
// 賦予hacha 查權(quán)限
grant select on schema::[dbo] to hacha;

無法進(jìn)行修改/增加等操作，因?yàn)闆]有權(quán)限

否則一直會(huì)報(bào)如下錯(cuò)誤：

消息 15151，級(jí)別 16，狀態(tài) 1，第 5 行
無法對(duì) 用戶 'hacha' 執(zhí)行 查找，因?yàn)樗淮嬖冢蛘吣鷽]有所需的權(quán)限。

3. 查詢當(dāng)前用戶的用戶名

在SQL Server中，你可以使用CURRENT_USER或SYSTEM_USER函數(shù)來獲取當(dāng)前用戶的用戶名。

這些函數(shù)返回當(dāng)前SQL Server會(huì)話的數(shù)據(jù)庫用戶名。

以下是查詢當(dāng)前用戶名的SQL語句：

SELECT CURRENT_USER AS CurrentUserName;

或者

SELECT SYSTEM_USER AS CurrentUserName;

這兩個(gè)函數(shù)在大多數(shù)情況下會(huì)返回相同的結(jié)果，但它們的語義略有不同：

CURRENT_USER返回當(dāng)前執(zhí)行上下文的數(shù)據(jù)庫用戶名。如果在執(zhí)行存儲(chǔ)過程或觸發(fā)器時(shí)更改了執(zhí)行上下文（例如，使用EXECUTE AS），則CURRENT_USER可能會(huì)返回不同的值。

SYSTEM_USER返回與當(dāng)前SQL Server會(huì)話關(guān)聯(lián)的原始登錄名。它不會(huì)受到執(zhí)行上下文更改的影響。

在大多數(shù)日常場(chǎng)景中，CURRENT_USER和SYSTEM_USER將返回相同的結(jié)果，因?yàn)樗鼈兌即懋?dāng)前會(huì)話的用戶。

然而，在涉及執(zhí)行上下文更改的高級(jí)場(chǎng)景中，它們可能有所不同。

示例：
當(dāng)您看到 CURRENT_USER 返回 dbo 而 SYSTEM_USER 返回 sa 時(shí)，這通常意味著：
您使用 sa 登錄名登錄到 SQL Server。
查詢是在一個(gè)上下文中執(zhí)行的，其中當(dāng)前用戶被映射為 dbo 用戶。這可能是因?yàn)椴樵兪菑囊粋€(gè)存儲(chǔ)過程、觸發(fā)器或使用了 EXECUTE AS 的其他上下文中執(zhí)行的，或者是因?yàn)楫?dāng)前數(shù)據(jù)庫的安全設(shè)置使得 sa 登錄名被映射為 dbo 用戶。
dbo 是一個(gè)特殊的數(shù)據(jù)庫用戶，通常擁有數(shù)據(jù)庫中的大部分權(quán)限（盡管不是所有權(quán)限，特別是如果數(shù)據(jù)庫啟用了某些限制性的安全選項(xiàng)）。而 sa 是一個(gè)具有服務(wù)器范圍內(nèi)幾乎所有權(quán)限的登錄名。

如果你想要獲取SQL Server登錄名（而不是數(shù)據(jù)庫用戶名），你可以查詢sys.dm_exec_sessions動(dòng)態(tài)管理視圖（DMV），結(jié)合sys.server_principals目錄視圖：

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

這里，@@SPID是一個(gè)系統(tǒng)函數(shù)，它返回當(dāng)前會(huì)話的進(jìn)程ID（session ID），sys.dm_exec_sessions包含有關(guān)當(dāng)前服務(wù)器上所有會(huì)話的信息。

請(qǐng)注意，執(zhí)行上述查詢可能需要特定的權(quán)限，具體取決于你的SQL Server配置和角色成員資格。如果你沒有足夠的權(quán)限，你可能需要聯(lián)系數(shù)據(jù)庫管理員來獲取所需的信息。

4. 給用戶賦予數(shù)據(jù)庫的權(quán)限

• 創(chuàng)建或確認(rèn)用戶存在
• 分配數(shù)據(jù)庫權(quán)限
  一旦用戶存在，你就可以給它分配數(shù)據(jù)庫級(jí)別的權(quán)限了。這可以通過 GRANT 語句完成。

分配基本的數(shù)據(jù)操作權(quán)限（如 SELECT, INSERT, UPDATE, DELETE）：

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

這將授予用戶在 dbo 模式下對(duì)表進(jìn)行基本的 CRUD 操作。

如果你想針對(duì)特定的表而不是整個(gè)模式進(jìn)行授權(quán)，可以替換 SCHEMA::[dbo] 為具體的表名，例如 OBJECT::[dbo].[YourTableName]。

分配更高級(jí)別的權(quán)限（如 ALTER, EXECUTE）：

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

這將允許用戶修改模式和執(zhí)行存儲(chǔ)過程或函數(shù)。

分配數(shù)據(jù)庫級(jí)別的權(quán)限（如 BACKUP DATABASE, CREATE TABLE）：

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

這將允許用戶備份數(shù)據(jù)庫和創(chuàng)建新表。

• 考慮使用角色來管理權(quán)限
  為了簡(jiǎn)化權(quán)限管理，你可以考慮使用數(shù)據(jù)庫角色。
  你可以創(chuàng)建自定義角色，將權(quán)限分配給這些角色，然后將用戶添加到這些角色中。
  這樣，當(dāng)你需要更改用戶的權(quán)限時(shí)，只需更改角色的權(quán)限即可，而不必單獨(dú)修改每個(gè)用戶的權(quán)限。

創(chuàng)建角色并分配權(quán)限：

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

將用戶添加到角色：

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

注意事項(xiàng)：

確保在執(zhí)行這些操作時(shí)具有足夠的權(quán)限。通常需要是 db_owner、db_securityadmin 或具有類似權(quán)限的用戶。

仔細(xì)考慮授予的權(quán)限級(jí)別，避免授予不必要的權(quán)限，以減少潛在的安全風(fēng)險(xiǎn)。

在生產(chǎn)環(huán)境中更改權(quán)限之前，最好先在測(cè)試環(huán)境中驗(yàn)證更改的影響。

5. 查看當(dāng)前SQL Sever登錄名

在 SQL Server 中，要查看當(dāng)前實(shí)例中存在的所有用戶（登錄名），你可以查詢 sys.server_principals 系統(tǒng)視圖。
這個(gè)視圖包含了 SQL Server 實(shí)例中所有服務(wù)器級(jí)別的安全主體，包括登錄名、服務(wù)器角色和應(yīng)用程序角色。

以下是一個(gè)簡(jiǎn)單的 SQL 查詢，用于列出 SQL Server 實(shí)例中的所有登錄名（用戶）：

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');

這個(gè)查詢會(huì)返回所有類型的登錄名，包括 SQL 登錄名、Windows 登錄名、Windows 組、證書登錄名、非對(duì)稱密鑰登錄名和外部登錄名。如果你只對(duì) SQL 登錄名和 Windows 登錄名感興趣，可以進(jìn)一步過濾 type_desc 的值。
如果你只想看 SQL 登錄名和 Windows 登錄名，可以使用以下查詢：

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

這個(gè)查詢將只返回 SQL 登錄名和 Windows 登錄名。

請(qǐng)注意，執(zhí)行這些查詢通常需要具有足夠的權(quán)限。如果你沒有足夠的權(quán)限，可能無法看到所有的登錄名。在這種情況下，你可能需要聯(lián)系你的數(shù)據(jù)庫管理員來獲取所需的信息。

此外，sys.server_principals 視圖中的 type_desc 列描述了主體的類型。

例如，SQL_LOGIN 表示一個(gè) SQL Server 登錄名，而 WINDOWS_LOGIN 表示一個(gè) Windows 登錄名。

通過查看這個(gè)列，你可以了解每個(gè)主體的類型。

6. SQLSever中有root賬號(hào)嗎？

在 SQL Server 中，并沒有一個(gè)名為 “root” 的默認(rèn)賬號(hào)。在 Unix 或 Linux 系統(tǒng)中，“root” 是超級(jí)用戶賬號(hào)，具有對(duì)系統(tǒng)的完全控制權(quán)。

但在 Windows 和 SQL Server 的環(huán)境中，概念略有不同。

在 SQL Server 中，擁有最高權(quán)限的賬號(hào)通常是 sa（系統(tǒng)管理員）賬號(hào)。這個(gè)賬號(hào)在 SQL Server 安裝過程中創(chuàng)建，并具有對(duì) SQL Server 實(shí)例的完全控制權(quán)。

與 Unix/Linux 中的 “root” 類似，sa 賬號(hào)應(yīng)該謹(jǐn)慎使用，并且其密碼應(yīng)該妥善保管，以防止未經(jīng)授權(quán)的訪問。

除了 sa 賬號(hào)外，SQL Server 還支持 Windows 身份驗(yàn)證模式，其中 Windows 域或本地組的成員可以作為 SQL Server 的登錄名。在這種情況下，具有適當(dāng) Windows 權(quán)限的用戶或組可以擁有對(duì) SQL Server 的訪問權(quán)限。

如果你正在尋找具有高級(jí)權(quán)限的賬號(hào)來管理 SQL Server，你應(yīng)該考慮使用 sa 賬號(hào)或具有適當(dāng)權(quán)限的 Windows 用戶或組。但是，請(qǐng)務(wù)必注意安全和權(quán)限管理的最佳實(shí)踐，以避免潛在的安全風(fēng)險(xiǎn)。

7. 報(bào)錯(cuò)：無法對(duì) 用戶 ‘hx’ 執(zhí)行 查找，因?yàn)樗淮嬖?，或者您沒有所需的權(quán)限。

使用 T-SQL 查詢：你可以使用以下 T-SQL 查詢來檢查 ‘hx’ 用戶是否存在：

sql
USE master;  
GO  
  
SELECT name, type_desc   
FROM sys.server_principals   
WHERE name = 'hx';

如果查詢返回了結(jié)果，那么 ‘hx’ 用戶確實(shí)存在。如果查詢沒有返回任何結(jié)果，那么用戶不存在，你需要?jiǎng)?chuàng)建它。

8. 不存在多行注釋

在 SQL Server 中，沒有像一些其他編程語言（如 C# 或 Java）那樣的多行注釋符號(hào)（例如 / … /）。
然而，你可以使用單行注釋符號(hào) – 來達(dá)到多行注釋的效果，只需要在每一行前面都加上 – 即可。
例如：

-- 這是第一行注釋  
-- 這是第二行注釋  
-- 這是第三行注釋  
  
SELECT * FROM YourTableName; -- 這仍然是一個(gè)單行注釋

如果你經(jīng)常需要寫多行注釋，并且覺得上面的方法不夠優(yōu)雅或不夠方便，你可以考慮使用存儲(chǔ)過程或腳本來管理你的 SQL 代碼，并在其中使用外部文本編輯器或 IDE 的多行注釋功能。然后，你可以將編輯好的 SQL 代碼復(fù)制到 SQL Server Management Studio (SSMS) 或其他查詢工具中執(zhí)行。

此外，一些第三方的 SQL 工具或插件可能提供了在 SQL Server 中使用多行注釋的功能，但這不是 SQL Server 本身的功能。

如果你確實(shí)需要在 SQL Server 中處理大量的多行注釋，并且想要更自動(dòng)化的方式，你可能需要考慮使用版本控制系統(tǒng)（如 Git）和源代碼編輯器（如 Visual Studio Code、SSMS 的插件等），它們通常都支持多行注釋的添加和移除。

9. 報(bào)錯(cuò)：無法對(duì) 用戶 ‘hx’ 執(zhí)行 刪除，因?yàn)樗淮嬖冢蛘吣鷽]有所需的權(quán)限。

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

drop user hx;

消息 15151，級(jí)別 16，狀態(tài) 1，第 2 行

無法對(duì) 用戶 ‘hx’ 執(zhí)行 刪除，因?yàn)樗淮嬖?，或者您沒有所需的權(quán)限。

根據(jù)你提供的信息，你試圖刪除一個(gè)名為 ‘hx’ 的用戶，但是收到了一個(gè)錯(cuò)誤消息，提示 ‘hx’ 不存在或你沒有足夠的權(quán)限。

首先，drop user 語句在 SQL Server 中用于從特定數(shù)據(jù)庫中刪除用戶，而不是從服務(wù)器級(jí)別刪除登錄名。

如果你嘗試使用 drop user 來刪除一個(gè)登錄名，你會(huì)收到一個(gè)錯(cuò)誤，因?yàn)榈卿浢陀脩羰莾蓚€(gè)不同的概念。

登錄名是在服務(wù)器級(jí)別定義的，而用戶則是在數(shù)據(jù)庫級(jí)別定義的，并與登錄名關(guān)聯(lián)。

在你之前的查詢中，你使用 sys.server_principals 視圖列出了所有的登錄名，包括 SQL 登錄名、Windows 登錄名和 Windows 組。
結(jié)果顯示 ‘hx’ 是一個(gè) SQL_LOGIN 類型的登錄名。

要?jiǎng)h除一個(gè)登錄名，你應(yīng)該使用 DROP LOGIN 語句，而不是 DROP USER。

所以，你應(yīng)該執(zhí)行以下命令來刪除 ‘hx’ 登錄名：

USE master;  
GO  
  
DROP LOGIN hx;  
GO

請(qǐng)注意，執(zhí)行這個(gè)操作需要你有足夠的權(quán)限。通常，只有 sysadmin 固定服務(wù)器角色的成員或具有相應(yīng)權(quán)限的用戶才能刪除登錄名。

如果你只是想從特定數(shù)據(jù)庫中刪除一個(gè)用戶（假設(shè)該用戶與 ‘hx’ 登錄名關(guān)聯(lián)），你需要首先確保你連接到了正確的數(shù)據(jù)庫，然后執(zhí)行類似以下的命令：

USE YourDatabaseName;  
GO  
  
DROP USER [hx];  
GO

請(qǐng)?zhí)鎿Q YourDatabaseName 為你想要從中刪除用戶的數(shù)據(jù)庫名稱。

在嘗試刪除任何對(duì)象之前，請(qǐng)始終確保你了解這些操作的后果，并且已經(jīng)做好了適當(dāng)?shù)膫浞莺突謴?fù)計(jì)劃。

總結(jié)

到此這篇關(guān)于SQL Server數(shù)據(jù)庫用戶管理及權(quán)限管理的文章就介紹到這了,更多相關(guān)SQL Server用戶管理權(quán)限管理內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評(píng)論