情シス特化型メディア SYSZO

投稿日時 - 2017.04.19 11:26:42

igonさん

SQLServerのユーザー・ロールをスクリプト化

お世話になっております。
基本的な内容のような気がしており恐縮ですが、どなたかご教授お願いします。

DBMSとしてSQLServer2014を使用しております。

基幹システムの本番用DBと検証用DBがあり、検証用DBにのみ存在するユーザー・ロールがあります。
(検証用DBは最悪どうなっても大丈夫なため、CREATE権限等をつけたユーザーを一部利用者に渡しています。)

不定期に、検証用DBのデータを最新化するため、本番用DBのバックアップファイルで検証用DBを復元しています。
当然、検証用DBのユーザー・ロールがなくなってしまうため、現在は復元後に手で作成し直しています。

毎回やるのも面倒なため、スクリプトで出力しておこうと思ったのですが、どちらも以下のような一文のみでした。

------------------------------
CREATE USER [user_XXXXX] FOR LOGIN [user_XXXXX] WITH DEFAULT_SCHEMA=[dbo]
GO
CREATE ROLE [role_XXXXX]
------------------------------

設定している権限の内容が出力されていないため、このスクリプトを後で流しても期待どおりにはならなそうなのですが、どうしたら良いでしょうか?

1
いいね
2017.04.19 11:47
えいさん
そもそも、ロールへの権限付与をスクリプト(DDL)で行うようにすれば良いのではないでしょうか。
GRANTで権限付与は可能ですし、GUI上で可能な事はDDLで設定可能です。
(本来は逆で、DDLで可能な事をGUI化しているだけですが)

現状付与されている権限は、以下で可能です。
--指定したユーザー(ロール)の権限一覧を表示する
SELECT
USER_NAME(grantee_principal_id) AS ユーザー
,OBJECT_NAME(major_id) AS オブジェクト
,permission_name as 権限名
,state_desc as 権限の状態
FROM sys.database_permissions
WHERE grantee_principal_id = USER_ID('ユーザー名またはロール名')
2
いいね
2017.04.19 13:43
igonさん
えいさん

ありがとうございます。
GRANT含めてスクリプトとしてSQLServer側で出力してくれればなーと思っていたのですが、やはり作るべきですかね。

頂いた情報を参考に、下のようなGRANTを発行する自作スクリプトを持っておくことにします。
ありがとうございました。

DECLARE @user_role nvarchar(100) = ’role_XXXXX’
SELECT
(
SELECT
MAX(TMP.sql1)
FROM
(
SELECT
’GRANT ’ + permission_name + ’ to ’+@user_role+’ GO ’ AS sql1
FROM sys.database_permissions
WHERE grantee_principal_id = USER_ID(@user_role)
) AS TMP
GROUP BY TMP.sql1
FOR XML PATH(’’)
)
1
いいね
コメントする

ログインしてからコメントできます

急上昇ワード

更新2017.04.27 19:49

SYSZO応援企業