Amazon RDS for SQL Serverでのユーザー追加コマンド

Amazon RDS for SQL Serverで読み取り専用ユーザーと管理者用ユーザーを作成する機会がありましたので、作成する際に利用したコマンドを備忘録として残したいと思います。

１，読み取り専用ユーザーの作成

以下のコマンドを実行して、指定したDBの読み取りを許可したユーザーを追加します。

※NewUserName・myPassword・対象DB名は適宜変更

 --ログイン作成
 USE master;
 GO
 
 CREATE LOGIN [NewUserName]
 WITH PASSWORD = 'myPassword',
 CHECK_POLICY = OFF,CHECK_EXPIRATION = OFF;
 GO
 
 --対象DBへの接続許可
 USE [対象DB名];
 CREATE USER [NewUserName] FOR LOGIN [NewUserName];
 ALTER ROLE db_datareader ADD MEMBER [NewUserName];  
 GO

２，管理者用ユーザーの作成

以下のコマンドを実行してRDS管理用ユーザーを追加します。RDS管理用なので、DBの読み取り以外にDBの作成など実行できます。

※NewUserName・myPassword・対象DB名は適宜変更

--ログイン作成
 USE master;
 GO
 
 CREATE LOGIN [NewUserName]
 WITH PASSWORD = 'myPassword',
 CHECK_POLICY = OFF,CHECK_EXPIRATION = OFF;
 GO
 
 --システム権限
 --https://docs.aws.amazon.com/ja_jp/AmazonRDS/latest/UserGuide/UsingWithRDS.MasterAccounts.html
 GRANT ADMINISTER BULK OPERATIONS TO [NewUserName] WITH GRANT OPTION;
 GRANT ALTER ANY CONNECTION TO [NewUserName] WITH GRANT OPTION;
 GRANT ALTER ANY CREDENTIAL TO [NewUserName] WITH GRANT OPTION;
 GRANT ALTER ANY EVENT SESSION TO [NewUserName] WITH GRANT OPTION;
 GRANT ALTER ANY LINKED SERVER TO [NewUserName] WITH GRANT OPTION;
 GRANT ALTER ANY LOGIN TO [NewUserName] WITH GRANT OPTION;
 GRANT ALTER ANY SERVER AUDIT TO [NewUserName] WITH GRANT OPTION;
 GRANT ALTER ANY SERVER ROLE TO [NewUserName] WITH GRANT OPTION;
 GRANT ALTER SERVER STATE TO [NewUserName] WITH GRANT OPTION;
 GRANT ALTER TRACE TO [NewUserName] WITH GRANT OPTION;
 GRANT CONNECT SQL TO [NewUserName] WITH GRANT OPTION;
 GRANT CREATE ANY DATABASE TO [NewUserName] WITH GRANT OPTION;
 GRANT VIEW ANY DATABASE TO [NewUserName] WITH GRANT OPTION;
 GRANT VIEW ANY DEFINITION TO [NewUserName] WITH GRANT OPTION;
 GRANT VIEW SERVER STATE TO [NewUserName] WITH GRANT OPTION;
 
 --管理用サーバーロールの設定
 ALTER SERVER ROLE processadmin ADD MEMBER [NewUserName];
 ALTER SERVER ROLE setupadmin ADD MEMBER [NewUserName];
 GO
 
 --SQLAgentUser ロールへのユーザーの追加
 --https://docs.aws.amazon.com/ja_jp/AmazonRDS/latest/UserGuide/Appendix.SQLServer.CommonDBATasks.Agent.html
 EXECUTE msdb.dbo.rds_set_system_database_sync_objects @object_types = 'SQLAgentJob';
 USE [msdb];
 CREATE USER [NewUserName] FOR LOGIN [NewUserName];
 GO
 EXEC sp_addrolemember [SQLAgentUserRole], [NewUserName];
 
 --管理対象DBへの接続許可
 USE [対象DB名];
 CREATE USER [NewUserName] FOR LOGIN [NewUserName];
 ALTER ROLE db_owner ADD MEMBER [NewUserName];  
 GO