SQLserver コマンドでログイン・ユーザ作成

TAG

• SQlServer
• SQL Server Management Studio 19.0.2

補足 ログインユーザー と データベースユーザー について

• ログインユーザー : ログインするのに必要なユーザー

• データベースユーザー : データベース毎に作成するユーザー

登録の流れとポイント

1. ログインユーザー を 登録

ポイント

• 変数を宣言

.sql

DECLARE @sql NVARCHAR(MAX);
DECLARE @user_name NVARCHAR(MAX) = 'username';

• CREATE LOGIN で ログインユーザーを作成

.sql

SET @sql = 'CREATE LOGIN ' + QUOTENAME(@user_name) + ' WITH PASSWORD = ''Password'', DEFAULT_DATABASE = ' + QUOTENAME(@db_name) + ';';
EXEC sp_executesql @sql;

2. ログインユーザー を データベース に登録

ポイント

• CREATE USERでデータベース に １で作成したログインユーザーを登録する

.sql

SET @sql = 'CREATE USER ' + QUOTENAME(@user_name) + ' FOR LOGIN ' + QUOTENAME(@user_name) + ';';
EXEC sp_executesql @sql;

3. 権限付与

ポイント

• ALTER ROLE
• db_datareader : Read
• db_datawriter : Write
• db_execute : Execute※

.sql

SET @sql = 'ALTER ROLE db_datareader ADD MEMBER ' + QUOTENAME(@user_name) + ';';
EXEC sp_executesql @sql;

SET @sql = 'ALTER ROLE db_datawriter ADD MEMBER ' + QUOTENAME(@user_name) + ';';
EXEC sp_executesql @sql;

SET @sql = 'ALTER ROLE db_execute ADD MEMBER ' + QUOTENAME(@user_name) + ';';
EXEC sp_executesql @sql;

※db_executeの作成

.sql

-- ロールが存在しない場合にのみロールを作成し、権限を与える
IF NOT EXISTS (SELECT 1 FROM sys.database_principals WHERE name = 'db_execute' AND type = 'R')
BEGIN
    -- ロールを作成
    CREATE ROLE db_execute;
    -- 作成したロールに「実行」権限を与える
    GRANT EXECUTE TO db_execute;
END

その他 bat文法

実行確認処理

.bat

@echo off
SET /P ANSWER="ログインユーザを作成します。 (Y/N)？"

if /i {%ANSWER%}=={y} (goto :yes)
if /i {%ANSWER%}=={yes} (goto :yes)
EXIT
:yes

接続情報とSQLファイルの実行

.bat

REM #####################
REM ★接続情報
REM #####################
REM サーバー名(サーバーのPC名\インスタンス名)
set dbServer=192.168.xxx.xxx
REM データベース名
set dbName=test_db
REM ユーザー名
set userName=sa
REM パスワード
set password=testpass

REM #####################
REM ★SQLファイルを実行
REM #####################

sqlcmd -S %dbServer% -d %dbName% -U %userName% -P %password% -i create.sql >> log.txt

echo バッチを実行しました。
pause

その他 SQL文法

SQL発行手順

1. @sqlにSQL文を代入する
2. QUOTENAME()を使って変数を変換する※注意
3. EXEC sp_executesql @sql でSQLを実行する

.sql

SET @sql = 'ALTER ROLE db_datareader ADD MEMBER ' + QUOTENAME(@user_name) + ';';
EXEC sp_executesql @sql;

※注意
以下のように直接ステートメントに変数を使うとエラーになる

ALTER ROLE db_datareader ADD MEMBER @user_name;

変数宣言・代入

.sql

DECLARE @db_name NVARCHAR(128);
SET @db_name = 'test_db';

配列

.sql

DECLARE @user_names TABLE (Name VARCHAR(30));
INSERT INTO @user_names (Name) VALUES 
    ('TEST_01'),
    ('TEST_02'),
    ('TEST_03');

for

.sql

DECLARE userNameCursor CURSOR FOR
SELECT Name FROM @user_names;

OPEN userNameCursor;
FETCH NEXT FROM userNameCursor INTO @user_name;

WHILE @@FETCH_STATUS = 0
BEGIN
    DECLARE @sql NVARCHAR(max);

    -- ここに処理

END

CLOSE userNameCursor;
DEALLOCATE userNameCursor;