資料庫引擎權限使用者入門

適用於：SQL Server Azure SQL 資料庫 Azure SQL 受控執行個體 Azure Synapse Analytics Analytics Platform System (PDW)

透過登入與伺服器角色的伺服器等級，以及資料庫使用者與資料庫角色的資料庫等級，管理資料庫引擎中的權限。 適用於 SQL Database 的模型會在每個資料庫中公開同個系統，但不提供伺服器等級權限。 本文會檢閱一些基本的安全性概念，並接著說明典型的權限實作資訊。

注意

Microsoft Entra ID 先前稱為 Azure Active Directory (Azure AD)。

安全性主體

安全性主體是使用 SQL Server 且可獲指派權限採取動作之身分識別的正式名稱。 這些身分識別通常為人員或人員群組，但亦可為偽裝成人員的其他實體。 您可使用列示的 Transact-SQL 或是使用 SQL Server Management Studio，建立和管理安全性主體。

登入

登入係指用於登入 SQL Server 資料庫引擎的個別使用者帳戶。 SQL Server 和 SQL Database 支援根據 Windows 驗證的登入，以及根據 SQL Server 驗證的登入。 如需有關兩種登入類型的資訊，請參閱 Choose an Authentication Mode。

固定伺服器角色

在 SQL Server 中，固定伺服器角色係指一組預先設定的角色，其提供方便的伺服器等級權限群組。 您可使用 ALTER SERVER ROLE ... ADD MEMBER 陳述式將登入新增至角色。 如需詳細資訊，請參閱 ALTER SERVER ROLE (Transact-SQL)。 SQL Database 不支援固定伺服器角色，但在 master 資料庫中具有兩個可做為伺服器角色運作的角色 (dbmanager 和 loginmanager)。

使用者定義伺服器角色

在 SQL Server 中，您可建立專屬的伺服器角色並為其指派伺服器等級權限。 您可使用 ALTER SERVER ROLE ... ADD MEMBER 陳述式將登入新增至伺服器角色。 如需詳細資訊，請參閱 ALTER SERVER ROLE (Transact-SQL)。 SQL Database 不支援使用者定義伺服器角色。

資料庫使用者

在資料庫中建立資料庫使用者，並將該資料庫使用者對應至登入，以針對登入授與資料庫存取權。 資料庫使用者名稱通常會與登入名稱相同，但這兩種名稱不一定非得相同。 每個資料庫使用者皆會對應至單一登入。 登入僅可對應至一個資料庫中的單一使用者，但其可對應做為數個不同資料庫中的資料庫使用者。

您亦可建立未具備對應登入的資料庫使用者。 這些使用者稱為「自主資料庫使用者」。 Microsoft 建議您使用自主資料庫使用者，原因在於其可更輕鬆地將資料庫移至不同的伺服器。 自主資料庫與登入相似，可使用 Windows 驗證或 SQL Server 驗證。 如需詳細資訊，請參閱 自主的資料庫使用者 - 使資料庫可攜。

共有 12 個類型的使用者，其僅在驗證方式與顯示對象方面略有差異。 若要查看使用者清單，請參閱 CREATE USER (Transact-SQL)。

固定資料庫角色

固定伺服器角色係指一組預先設定的角色，其提供方便的資料庫等級權限群組。 您可使用 ALTER ROLE ... ADD MEMBER 陳述式，將資料庫使用者與使用者定義資料庫角色新增至固定資料庫角色。 如需詳細資訊，請參閱 ALTER ROLE (Transact-SQL)。

使用者定義資料庫角色

具有 CREATE ROLE 權限的使用者可建立新的使用者定義資料庫角色，以代表具有通用權限的使用者群組。 通常會針對整個角色授與或拒絕權限，以精簡權限管理與監視作業。 您可使用 ALTER ROLE ... ADD MEMBER 陳述式，將資料庫使用者新增至資料庫角色。 如需詳細資訊，請參閱 ALTER ROLE (Transact-SQL)。

其他主體

此處不討論其他安全性主體，包括應用程式角色，以及根據憑證或非對稱式索引鍵的登入和使用者。

如需顯示 Windows 使用者、Windows 群組、登入和資料庫使用者之間關係的說明圖，請參閱 Create a Database User。

典型案例

下列範例表示通用和建議的權限設定方法。

在 Windows Active Directory 或 Microsoft Entra ID 中

1. 為每個人員建立使用者。

2. 建立代表工作單位與工作職務的 Windows 群組。

3. 將 Windows 使用者新增至 Windows 群組。

若連接的人員將會連接至眾多資料庫

1. 針對 Windows 群組建立登入。 (若使用 SQL Server 驗證，請略過 Active Directory 步驟，並在此處建立 SQL Server 驗證登入。)

2. 在使用者資料庫中，建立代表 Windows 群組的登入資料庫使用者。

3. 在使用者資料庫中，建立一或多個使用者定義資料庫角色，每個角色皆代表類似的職務。 例如財務分析師和銷售分析師。

4. 將資料庫使用者新增至一或多個使用者定義資料庫角色。

5. 授與權限至使用者定義資料庫角色。

若連接的人員僅會連接至單一資料庫

1. 在使用者資料庫中，針對 Windows 群組建立自主資料庫。 (若使用 SQL Server 驗證，請略過 Active Directory 步驟，並在此處建立自主資料庫使用者 SQL Server 驗證。)

2. 在使用者資料庫中，建立一或多個使用者定義資料庫角色，每個角色皆代表類似的職務。 例如財務分析師和銷售分析師。

3. 將資料庫使用者新增至一或多個使用者定義資料庫角色。

4. 授與權限至使用者定義資料庫角色。

此時顯示的結果，通常會是屬於 Windows 群組成員的 Windows 使用者。 Windows 群組在 SQL Server 或 SQL Database 中具有登入。 登入會對應至使用者資料庫中的使用者身分識別。 使用者是資料庫角色的成員。 現在您必須新增權限至角色。

指派權限

大部分的權限陳述式皆具有以下格式︰

AUTHORIZATION PERMISSION ON SECURABLE::NAME TO PRINCIPAL;

• AUTHORIZATION 必須是 GRANT、 REVOKE 或 DENY。

• PERMISSION 會建立允許或禁止的動作。 SQL Server 與 SQL Database 之間的確切權限數目不同。 權限會列示在下面的權限 (資料庫引擎) 文章和參照圖表中。

• ON SECURABLE::NAME 是安全性實體類型 (伺服器、伺服器物件、資料庫或資料庫物件) 及其名稱。 某些權限不需要 ON SECURABLE::NAME，這是因為其在內容當中不明確或不適當。 例如，CREATE TABLE 權限不需要 ON SECURABLE::NAME 子句 (GRANT CREATE TABLE TO Mary; 允許 Mary 建立資料表)。

• PRINCIPAL 是接收或失去權限的安全性主體 (登入、使用者或角色)。 盡可能授與權限給角色。

下列 grant 陳述式範例，會將位於 Parts 資料表或檢視 (包含於 Production 結構描述) 中的 UPDATE 權限，授與至名為 PartsTeam 的角色：

GRANT UPDATE ON OBJECT::Production.Parts TO PartsTeam;

下列 grant 陳述式範例會對名為 ProductionTeam 的角色，授與 Production 結構描述 (以及此結構描述包含的任何資料表或檢視) 上的 UPDATE 權限，這是更有效率且更有銷路的指派權限方法 (與個人層級相比)：

GRANT UPDATE ON SCHEMA::Production TO ProductionTeam;

使用 GRANT 陳述式，將權限授與至安全性主體 (登入、使用者和角色)。 使用 DENY 命令明確拒絕權限。 使用 REVOKE 陳述式移除先前授與或拒絕的權限。 權限會累計，使用者會接收授與至使用者登入以及任何群組成員資格的所有權限；不過，任何權限拒絕皆會覆寫所有授與。

提示

常見的錯誤是嘗試使用 GRANT 而非 DENY 來移除 REVOKE。 此做法在使用者從多個來源接收權限時可能會造成問題；此為常見狀況。 下列範例將示範主體。

「銷售」群組透過陳述式 SELECT 接收 OrderStatus 資料表上的 GRANT SELECT ON OBJECT::OrderStatus TO Sales;權限。 使用者 Jae 是「銷售」角色的成員。 Jae 亦已透過陳述式 GRANT SELECT ON OBJECT::OrderStatus TO Jae;，在其名稱下獲得授與 OrderStatus 資料表的 SELECT 權限。 假設系統管理員想要針對「銷售」角色移除 GRANT 。

• 若系統管理員正確執行 REVOKE SELECT ON OBJECT::OrderStatus TO Sales;，則 Jae 會透過其個別的 GRANT 陳述式，保留 OrderStatus 資料表的 SELECT 存取權。

• 若系統管理員未正確執行 DENY SELECT ON OBJECT::OrderStatus TO Sales;，則 Jae 「銷售」角色成員的 SELECT 權限將會遭到拒絕，這是因為「銷售」的 DENY 會覆寫其個別的 GRANT。

注意

您可使用 Management Studio 來設定權限。 在 [物件總管] 中找到安全性實體，以滑鼠右鍵按一下安全性實體，然後選取 [屬性]。 選取 [權限] 頁面。 如需使用權限頁面的說明，請參閱 Permissions or Securables Page。

權限階層

權限具有父子式階層。 也就是說，若您在資料庫上授與 SELECT 權限，則該權限在資料庫中會包含所有 (子系) 結構描述的 SELECT 權限。 若您在結構描述上授與 SELECT 權限，則其在結構描述中會包含所有 (子系) 資料表的 SELECT 權限。 權限可轉移；亦即若您在資料庫上授與 SELECT 權限，則其會包含所有 (子系) 結構描述以及所有 (孫系) 資料表與檢視上的 SELECT 權限。

權限亦具有涵蓋的權限。 物件上的 CONTROL 權限通常會提供物件的其他所有權限。

由於父子式階層與涵蓋階層皆可做為同個權限，因此讓權限系統變得複雜。 例如就資料表 (區域) 而言，其會位於結構描述 (客戶) 與資料庫 (SalesDB) 中。

• CONTROL 權限會包含「區域」資料表上的其他所有權限，包括 ALTER、SELECT、INSERT、UPDATE、DELETE 及一些其他權限。

• SELECT ，會包含「區域」資料表上的 SELECT 權限。

因此，您可透過以下六種陳述式之一，在「區域」資料表上達成 SELECT 權限：

GRANT SELECT ON OBJECT::Region TO Jae;

GRANT CONTROL ON OBJECT::Region TO Jae;

GRANT SELECT ON SCHEMA::Customers TO Jae;

GRANT CONTROL ON SCHEMA::Customers TO Jae;

GRANT SELECT ON DATABASE::SalesDB TO Jae;

GRANT CONTROL ON DATABASE::SalesDB TO Jae;

授與最小權限

以上所列的第一個權限 (GRANT SELECT ON OBJECT::Region TO Jae;) 最為細微，亦即該陳述式是可授與 SELECT的最小權限。 其並無任何從屬物件權限。 最好以一律授與最低權限為原則 (您可以深入了解最低權限原則)，但同時 (有點牴觸) 嘗試在較高層級上授與權限，以簡化授與系統。 因此，若 Jae 需要整個結構描述的權限，則只要在結構描述等級授與一次 SELECT 即可，而無須在資料表或檢視等級多次授與 SELECT。 資料庫的設計可能會大幅影響此策略的成功程度。 若資料庫經過設計，在單一結構描述中包含需要相同權限的物件，則最能發揮此策略的優勢。

提示

設計資料庫及其物件時，請從一開始就規劃誰或哪些應用程式會存取哪些物件，並以此為依據，盡可能根據存取類型的貯體在結構描述中放置物件 (也就是資料表、檢視、函式和預存程序)。

權限圖表

下圖顯示這些權限及其彼此間的關聯性。 一些較高等級的權限 (例如 CONTROL SERVER) 會多次列出。 本文的海報字級太小，無法閱讀。 您可以下載 PDF 格式的全尺寸資料庫引擎權限海報。

如需顯示資料庫引擎主體與伺服器和資料庫物件間關係的說明圖，請參閱權限階層 (資料庫引擎)。

權限與固定伺服器與固定資料庫角色

固定伺服器角色與固定資料庫角色的權限近似，但並非完全相同的細微權限。 例如，sysadmin 固定伺服器角色的成員具有 SQL Server 執行個體的所有權限，這是因為其是以 CONTROL SERVER 權限登入。 不過，授與 CONTROL SERVER 權限並不會登入 sysadmin 固定伺服器角色成員，而新增登入至 sysadmin 固定伺服器角色並不會明確授與登入 CONTROL SERVER 權限。 有時，預存程序在檢查權限時會檢查固定角色，但不會檢查細微權限。 例如，卸離資料庫需要具有 db_owner 固定資料庫角色的成員資格。 對等的 CONTROL DATABASE 權限不足。 這兩個系統採平行方式運作，但彼此鮮少互動。 Microsoft 建議盡可能使用最新且更細微的權限系統，來取代固定角色。

監視權限

下列檢視會傳回安全性資訊。

• 您可使用 sys.server_principals 檢視，檢驗伺服器上的登入與使用者定義伺服器角色。 SQL Database 中不提供此檢視。

• 您可使用 sys.database_principals 檢視，檢驗資料庫中的使用者與使用者定義角色。

• 您可使用 sys.server_permissions 檢視，檢驗授與至登入以及使用者定義伺服器角色的權限。 SQL Database 中不提供此檢視。

• 您可使用 sys.database_permissions 檢視，檢驗授與至使用者以及使用者定義固定資料庫角色的權限。

• 您可使用 sys.database_role_members 檢視來檢驗資料庫角色成員資格。

• 您可使用 sys.server_role_members 檢視來檢驗伺服器角色成員資格。 SQL Database 中不提供此檢視。

• 如需其他安全性相關檢視，請參閱安全性目錄檢視 (Transact-SQL)。

範例

下列陳述式會傳回關於權限的實用資訊。

A. 每個使用者的資料庫權限清單

若要傳回資料庫中已授與或拒絕的明確權限 (SQL Server 和 SQL Database)，請在資料庫中執行下列陳述式。

SELECT
    perms.state_desc AS State,
    permission_name AS [Permission],
    obj.name AS [on Object],
    dp.name AS [to User Name]
FROM sys.database_permissions AS perms
JOIN sys.database_principals AS dp
    ON perms.grantee_principal_id = dp.principal_id
JOIN sys.objects AS obj
    ON perms.major_id = obj.object_id;

B. 列出伺服器角色成員

若要傳回伺服器角色的成員 (僅限 SQL Server)，請執行下列陳述式。

SELECT roles.principal_id AS RolePrincipalID,
    roles.name AS RolePrincipalName,
    server_role_members.member_principal_id AS MemberPrincipalID,
    members.name AS MemberPrincipalName
FROM sys.server_role_members AS server_role_members
INNER JOIN sys.server_principals AS roles
    ON server_role_members.role_principal_id = roles.principal_id
LEFT JOIN sys.server_principals AS members
    ON server_role_members.member_principal_id = members.principal_id;

C. 列出屬於資料庫層級角色成員的所有資料庫主體

若要傳回資料庫角色的成員 (SQL Server 和 SQL Database)，請在資料庫中執行下列陳述式。

SELECT dRole.name AS [Database Role Name], dp.name AS [Members]
FROM sys.database_role_members AS dRo
JOIN sys.database_principals AS dp
    ON dRo.member_principal_id = dp.principal_id
JOIN sys.database_principals AS dRole
    ON dRo.role_principal_id = dRole.principal_id;

另請參閱

• SQL Server Database Engine 和 Azure SQL Database 的資訊安全中心
• 安全性函數 (Transact-SQL)
• 安全性相關的動態管理檢視和函數 (Transact-SQL)
• 安全性目錄檢視 (Transact-SQL)
• sys.fn_builtin_permissions (Transact-SQL)
• 判斷有效的 Database Engine 權限

下一步

• 教學課程：資料庫引擎使用者入門
• 建立資料庫 (教學課程)
• 教學課程：SQL Server Management Studio
• 教學課程：撰寫 Transact-SQL 陳述式