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

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

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

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

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

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

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

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

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];

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

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

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

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

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

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

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

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

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

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

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

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

當(dāng)我們創(chuàng)建登錄名+密碼后，就可以通過輸入密碼的方式，以該登錄名登錄數(shù)據(jù)庫
當(dāng)我們創(chuàng)建用戶名后，想要訪問數(shù)據(jù)庫，需要在數(shù)據(jù)庫下創(chuàng)建對應(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)行修改/增加等操作，因為沒有權(quán)限

否則一直會報如下錯誤：

消息 15151，級別 16，狀態(tài) 1，第 5 行
無法對 用戶 'hacha' 執(zhí)行 查找，因為它不存在，或者您沒有所需的權(quán)限。

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

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

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

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

SELECT CURRENT_USER AS CurrentUserName;

或者

SELECT SYSTEM_USER AS CurrentUserName;

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

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

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

在大多數(shù)日常場景中，CURRENT_USER和SYSTEM_USER將返回相同的結(jié)果，因為它們都代表當(dāng)前會話的用戶。

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

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

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

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

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

請注意，執(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ù)庫級別的權(quán)限了。這可以通過 GRANT 語句完成。

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

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

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

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

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

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

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

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

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

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

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

注意事項：

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

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

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

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

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

以下是一個簡單的 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');

這個查詢會返回所有類型的登錄名，包括 SQL 登錄名、Windows 登錄名、Windows 組、證書登錄名、非對稱密鑰登錄名和外部登錄名。如果你只對 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');

這個查詢將只返回 SQL 登錄名和 Windows 登錄名。

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

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

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

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

6. SQLSever中有root賬號嗎？

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

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

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

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

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

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

7. 報錯：無法對 用戶 ‘hx’ 執(zhí)行 查找，因為它不存在，或者您沒有所需的權(quán)限。

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

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

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

8. 不存在多行注釋

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

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

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

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

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

9. 報錯：無法對 用戶 ‘hx’ 執(zhí)行 刪除，因為它不存在，或者您沒有所需的權(quán)限。

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

drop user hx;

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

無法對 用戶 ‘hx’ 執(zhí)行 刪除，因為它不存在，或者您沒有所需的權(quán)限。

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

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

如果你嘗試使用 drop user 來刪除一個登錄名，你會收到一個錯誤，因為登錄名和用戶是兩個不同的概念。

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

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

要刪除一個登錄名，你應(yīng)該使用 DROP LOGIN 語句，而不是 DROP USER。

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

USE master;  
GO  
  
DROP LOGIN hx;  
GO

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

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

USE YourDatabaseName;  
GO  
  
DROP USER [hx];  
GO

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

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

總結(jié)

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

最新評論