!!!Microsoft SQL Server 権限付与
{{category SQL Server,nolink}}権限付与のメモ

* 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)