質問

2017年04月19日 11時26分
  • SQLServerのユーザー・ロールをスクリプト化

情シスのオープンナレッジ『Syszo』サービス終了のお知らせ

質問

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

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

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

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

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


CREATE USER [userXXXXX] FOR LOGIN [userXXXXX] WITH DEFAULT_SCHEMA=[dbo]
GO

CREATE ROLE [role_XXXXX]

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

2件の回答があります

回答

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

現状付与されている権限は、以下で可能です。
--指定したユーザー(ロール)の権限一覧を表示する
SELECT
USERNAME(granteeprincipalid) AS ユーザー
,OBJECT
NAME(majorid) AS オブジェクト
,permission
name as 権限名
,statedesc as 権限の状態
FROM sys.database
permissions
WHERE granteeprincipalid = USER_ID('ユーザー名またはロール名')

2017年04月19日 11時47分

回答

えいさん

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

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

DECLARE @userrole nvarchar(100) = ’roleXXXXX’
SELECT
(
SELECT
MAX(TMP.sql1)
FROM
(
SELECT
’GRANT ’ + permissionname + ’ to ’+@userrole+’ GO ’ AS sql1
FROM sys.databasepermissions
WHERE grantee
principalid = USERID(@user_role)
) AS TMP
GROUP BY TMP.sql1
FOR XML PATH(’’)
)

2017年04月19日 13時43分

あなたもコメントしましょう!