SQL Serverでログインユーザーの権限を確認してみた｜ITエンジニアとして経験・学習したこと

SQL Serverでログインユーザーの権限を確認するには、sys.fn_my_permissionsビュー等で確認できる。今回は、ログインユーザーのデータベース権限やサーバー権限を確認してみたので、その手順を共有する。

前提条件

下記記事の「前提条件」の内容が完了していること。

Spring BootでSQL Serverに接続しMyBatisを利用してみた今回は、Spring Bootアプリケーションで接続するデータベースをSQL Serverに変更してみたので、そのサンプルプログラムを共...

ログインユーザーの権限確認

ログインユーザーの権限確認を行った結果は、以下の通り。

1) ログインユーザーのデータベース権限は、以下のSQLで確認できる。

SELECT * FROM sys.fn_my_permissions(NULL, 'DATABASE')

＜抽出データ全量＞

2) 1)でDML(SELECT, UPDATE等)に加え、DDL(CREATE TABLE等)の幅広い権限が付与されているが、これは、ログインユーザーであるUSER01に、db_ownerというデータベースロールが付与されているためである。ログインユーザーのデータベースロールは、以下のSQLで確認できる。

SELECT DP1.name AS DatabaseRoleName,   
   isnull (DP2.name, 'No members') AS DatabaseUserName   
 FROM sys.database_role_members AS DRM  
 RIGHT OUTER JOIN sys.database_principals AS DP1  
   ON DRM.role_principal_id = DP1.principal_id  
 LEFT OUTER JOIN sys.database_principals AS DP2  
   ON DRM.member_principal_id = DP2.principal_id  
WHERE DP1.type = 'R'
ORDER BY DP1.name

なお、データベースロールについては、以下のサイトを参照のこと。
https://learn.microsoft.com/ja-jp/sql/relational-databases/security/authentication-access/database-level-roles?view=sql-server-ver17

3) ログインユーザーの特定テーブル(下記例ではdbo.user_data)の権限は、以下のSQLで確認できる。

SELECT * FROM sys.fn_my_permissions('dbo.user_data','object')

＜抽出データ全量＞

4) ログインユーザーのサーバー権限は、以下のSQLで確認できる。

SELECT * FROM sys.fn_my_permissions(NULL, 'SERVER')

＜抽出データ全量＞

5) 4)で幅広い権限が付与されているが、これは、ログインユーザーであるUSER01に、sysadminというサーバーロールが付与されているためである。ログインユーザーのサーバーロールは、以下のSQLで確認できる。

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
INNER JOIN sys.server_principals AS members 
    ON server_role_members.member_principal_id = members.principal_id