MSSQL/GRANT

Microsoft SQL Server 権限付与

権限付与のメモ

• GRANT オブジェクト権限 (Transact-SQL) - SQL Server | Microsoft Learn
  • https://learn.microsoft.com/ja-jp/sql/t-sql/statements/grant-object-permissions-transact-sql?view=sql-server-ver17
• GRANT (スキーマ権限の許可) (Transact-SQL) - SQL Server | Microsoft Learn
  • https://learn.microsoft.com/ja-jp/sql/t-sql/statements/grant-schema-permissions-transact-sql?view=sql-server-ver17
• GRANT (データベースの権限の許可) (Transact-SQL) - SQL Server | Microsoft Learn
  • https://learn.microsoft.com/ja-jp/sql/t-sql/statements/grant-database-permissions-transact-sql?view=sql-server-ver17

  権限確認

-- データベースレベルの権限確認
SELECT DB_NAME() AS DB_NAME
, state_desc AS state -- 権限の状態
, permission_name AS permission -- 権限
, class_desc AS [class] -- リソースクラス
, OBJECT_NAME(major_id) AS securable -- オブジェクト名
--, USER_NAME(grantee_principal_id) AS principal
, m.name AS principal -- ユーザー名またはロール名
, m.type_desc AS [principal type] -- ユーザー or ロール
FROM sys.database_permissions p
LEFT JOIN sys.database_principals m
ON m.principal_id = p.grantee_principal_id
where grantee_principal_id<>0 -- public db role
--and major_id = OBJECT_ID('object name')
--and grantee_principal_id = USER_ID('user name')
ORDER BY grantee_principal_id, major_id, permission_name, state;

-- データベースロール メンバー
SELECT DB_NAME() AS DB_NAME
, r.name AS role_name, m.name AS member_name
FROM sys.database_role_members rm
LEFT JOIN sys.database_principals r
ON r.principal_id = rm.role_principal_id
LEFT JOIN sys.database_principals m
ON m.principal_id = rm.member_principal_id
--where r.name = 'db_owner'
ORDER BY r.name, m.name;

-- サーバーロール メンバー
SELECT @@SERVERNAME+'\'+@@SERVICENAME AS INSTANCE_NAME
, r.name AS role_name, m.name AS member_name
FROM sys.server_role_members rm
LEFT JOIN sys.server_principals r
ON r.principal_id = rm.role_principal_id
LEFT JOIN sys.server_principals m
ON m.principal_id = rm.member_principal_id
--where r.name = 'sysadmin'
ORDER BY r.name, m.name;

-- イスタンスレベルの権限確認
SELECT @@SERVERNAME+'\'+@@SERVICENAME AS INSTANCE_NAME
, state_desc AS state -- 権限の状態
, permission_name AS permission -- 権限
, class_desc AS [class] -- リソースクラス
--, USER_NAME(grantee_principal_id) AS principal
, m.name AS principal -- ユーザー名またはロール名
, m.type_desc AS [principal type]
FROM sys.server_permissions p
LEFT JOIN sys.server_principals m
ON m.principal_id = p.grantee_principal_id
ORDER BY grantee_principal_id, major_id, permission_name, state;

  ストアド プロシージャ・関数（ファンクション）の実行

参照ユーザ（sysadmin サーバロールや db_owner データベース ロール を持たない）は、各データベースのストアド・ファンクションの実行権限がない。

そのため、実行権限（EXECUTE）の付与が必要となる。

use [db name]
GO
GRANT EXECUTE ON [dbo].[Function or StoredProcedure] TO [user name]
GO

ストアドに権限を付与する

USE 《db name》;
GRANT EXECUTE ON OBJECT::《schema name》.《object name》 TO 《user name》;
GO

スキーマのすべてのストアドに権限を付与する

MSSQL は、スキーマに権限付与することで、再帰的にその中のオブジェクトに自動的に権限が継承される。ただ、不要な権限を与えないよう、必要なプロシージャのみに絞って権限付与することが推奨される。

USE 《db name》;
GRANT EXECUTE ON SCHEMA::《schema name》 TO 《user name》;
GO

データベース ロール で、すべてのストアドの権限を付与する

固定データベース ロール db_datareader と同じようにデータベース内すべてを対象にする（やりすぎな気はする）。で、そのロールにユーザをメンバー追加する。

USE <database-name>;

CREATE ROLE db_executor;

GRANT EXECUTE ON DATABASE::<database-name> TO db_executor;

ALTER ROLE db_executor ADD MEMBER [user name];

  利用状況モニターに必要な権限付与

DBA権限（sysadmin サーバロール）を持たないユーザで、SQL Server Management Studio (SSMS) の「利用状況モニター」を使用するのに必要な権限「VIEW SERVER STATE」および「VIEW SERVER PERFORMANCE STATE」(MSSQL 2022以降)

USE master;
GRANT VIEW SERVER STATE TO [user name];
GRANT VIEW SERVER PERFORMANCE STATE TO [user name];
GO

※データファイルIO などは、さらに別の権限が必要。※2022以降は、SERVER PERFORMANCE STATE ってあったけど、エラーになった気がする… サーバが 2022 以前だった…

  レポート に必要な権限付与

DBA権限（sysadmin サーバロール）を持たないユーザで、SQL Server Management Studio (SSMS) のレポート出力に必要な権限「VIEW SERVER STATE」および「ALTER TRACE」。

USE master;
GRANT VIEW SERVER STATE TO [user name];
GRANT ALTER TRACE TO [user name];
GO

「パフォーマンス ダッシュボード」は、SERVER STATE と TRACE のみ。

「サーバー ダッシュボード」の「構成の詳細」は、msdb の dbo.sysjobs の SELECT が必要？起動時刻、インスタンス名、バージョン・エディション、サーバーの照合順序 などが、「The SELECT permission was denied on the object 'sysjobs', database 'msdb', schema 'dbo'.」となり、表示されない。

データベースの「ディスク使用量」で、「データ File で使用されているディスク領域」は、ドキュメントにない「DBCC showfilestats」と言うコマンドが使用されている（ db_owner ロールが必要）。

  実行計画

実行計画を表示するには、T-SQL を実行するための適切な権限（SELECT 等）が与えられている必要がある。また、SHOWPLAN 権限が、参照されるオブジェクトを含むすべてのデータベースで必要になる。

USE <database-name>;
GRANT SHOWPLAN TO <database_principal>;

• SHOWPLAN 権限の許可、拒否、および取り消しの構文 | Microsoft Learn
  • https://learn.microsoft.com/ja-jp/previous-versions/sql/sql-server-2008-r2/ms175117(v=sql.105)