命名って難しい

変数、関数、クラスなどなど実装より命名に毎回悩むタイプの人間による技術についてのメモ。

SQL Serverのセキュリティ保護可能なリソース、全部出す。

2019/09/18 追記

取得ということに焦点を当てて検索してコピペで作ったけれど、今後は sys.database_permissions をきっちり見ていく必要があるなと思った。今回は許可のみ(現在のシステムがそうなっている)で考えていたけれど、拒否もあれば permission stateも表示すると正確に把握できる。

docs.microsoft.com


引き継いだシステムの検証用データベースの構築をしたのですが、セキュリティ保護可能なリソースの設定漏れが発生したので再発防止のため、表題のクエリを作成した。

SSMSで一つづつポチポチプロパティを変更して設定していったため作業漏れが発生したので、まず一覧がほしいと思ったのでそこまで。 将来的には権限を設定するクエリも作成しておいて、検証用DBの作成が楽になるといいな。

日英表現違いに注意

URLとか英語で読むにしたら分かるんですが、セキュリティ保護可能なリソースは英語で securables です。

docs.microsoft.com

securables でググるだけでもかなり情報へのアクセスしやすさが変わりました。

クエリ

一時テーブルはめっちゃ適当です。

CREATE TABLE #secuarables_alldb (
    server_name text,
    database_name text,
    object_name text,
    user_name text,
    permission_name text
);

EXEC sp_MSforeachdb '
USE ?;

INSERT INTO #secuarables_alldb
SELECT
   @@servername as server_name, ''?'' AS database_name, OBJECT_NAME(major_id) as object_name, USER_NAME(grantee_principal_id) as user_name, permission_name
FROM
    sys.database_permissions p
WHERE
    p.class = 1 AND
    OBJECTPROPERTY(major_id, ''IsMSSHipped'') = 0
ORDER BY
    OBJECT_NAME(major_id), USER_NAME(grantee_principal_id), permission_name
';


select * FROM #secuarables_alldb;
DROP table #secuarables_alldb;

参考

sp_MSforeachdb

めっちゃ便利だった。こんなものがあるとは。。。
勉強の過程でこういうのってどうやってたどり着くんでしょうね。

blog.engineer-memo.com

Securables の取得

stackoverflow.com