Zenn
📝

SQL Serverの認証モード変更＆sysadminロールのユーザー作成

2023/12/07に公開
CLI
SQL Server
コマンド
sqlcmd
tech

こんにちは、$@(ダラ～っと)です(๑Ò﹃ Ó๑)ゝ

はじめに

久しぶりにSQL Serverを使った時に、SSMSが使えなかったのでコマンドラインでSQLServer認証にするのに手間取ったので、メモを残します。
（会社のPCが容量不足という今どきこんなことで！？という悩み…）

SSMSが使えるなら、下記から変更できます。
https://learn.microsoft.com/ja-jp/sql/relational-databases/security/choose-an-authentication-mode?view=sql-server-ver15

何がわかる？

SQL Server 2019 Expressのデフォルト設定からSSMSを使用せずに下記を行う。

  • 認証モード：混合モード (SQL Server 認証モードと Windows 認証モード)に変更する。
  • ユーザー：sysadminロールのユーザー作成する。

環境

  • windows 11 Pro
  • SQL Server 2019 Express

認証モードの変更

やることは、下記のレジストリを変更する。
<インスタンスID>は、環境によって異なります。

項目 値
キー HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server<インスタンスID>\MSSQLServer
値名 LoginMode
値 1:Windows認証のみ
2:混合モード (Windows 認証モードとSQL Server 認証モード)

  1. <インスタンスID>を調べる。
    デフォルトのインスタンスを使用する場合は、MSSQL{nn}.MyInstanceの形式で作成されています。
    {nn}はバージョンにより異なるので、(公式)Shared Files for All Instances of SQL Serverから取得します。
    2023/12/07時点では、下記になります。

    Version {nn}
    SQL Server 2022 (16.x) 16
    SQL Server 2019 (15.x) 15
    SQL Server 2017 (14.x) 14
    SQL Server 2016 (13.x) 13
    SQL Server 2014 (12.x) 12
    SQL Server 2012 (11.x) 11
    • ex)SQL Server 2019 Expressの場合は、MSSQL15.MyInstanceです。

  2. レジストリを変更する。
    regeditからでも良いですが、コマンドプロンプト（管理者）から変更します。
    (Windowsキー + Rキー→cmdを入力→Ctrl + Shift + Enterで管理者権限でコマンドプロンプトを開きます)

    形式
    REG ADD "HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\<インスタンスID>\MSSQLServer" /v LoginMode /t REG_DWORD /d <認証モード> /f
    • <インスタンスID>：上記1で調べたインスタンスID
    • <認証モード>：Windows認証のみの場合は1、混合モード (Windows 認証モードとSQL Server 認証モード)は2

    ex)SQL Server 2019 Expressを混合モードにする

    SQL Server 2019 Expressを混合モードにする
    REG ADD "HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL15.SQLEXPRESS\MSSQLServer" /v LoginMode /t REG_DWORD /d 2 /f

  3. SQL Serverを再起動する。
    SQL Server 2019 Expressの場合は、サービスのSQL Server (SQLEXPRESS)を再起動する。
    コマンドプロンプト（管理者権限）からは、下記で再起動します。

    SQL Server (SQLEXPRESS)の再起動
    net stop "SQL Server (SQLEXPRESS)"
net start "SQL Server (SQLEXPRESS)"

sysadminロールのユーザー作成

sqlcmdコマンドでSQL Serverにログインして作成します。

  1. 統合認証でログイン
    権限は、Windowsのアカウントに従います。管理者ユーザーで実行してください。

    Windows認証でログイン
    sqlcmd -E -S .\SQLEXPRESS
    • -E：Windows認証
    • -S：接続するサーバ名

    参考に、ChatGPTに管理者と標準の違いを聞いてみました。

    権限/操作 管理者ユーザーアカウント 標準ユーザーアカウント
    SQL Serverへの接続 接続可能 接続可能
    データベースの作成・削除 可能 不可
    データベースへのアクセス データベースの所有者として接続可能 データベースへのアクセス権限が必要
    システムデータベースへのアクセス 接続可能 接続可能
    サーバー レベルの構成変更 可能 一部の構成変更は可能、一部は不可
    ログインの作成・削除 可能 不可
    ユーザーおよびロールの作成・削除 可能 不可
    データベース オブジェクトの作成・削除 可能 データベースのアクセス権限に依存
    SQL Serverエージェントジョブの作成・実行 可能 不可

  2. ユーザーを作成してsysadminロールを設定する

    • SQL Server 認証モードの場合
      <UserName>とpasswordを設定して実行します。
    SQL Server 認証ユーザー作成
    -- ユーザー作成
CREATE LOGIN <UserName> WITH PASSWORD = 'Password';
GO
-- ユーザーにsysadminロールを設定
ALTER SERVER ROLE sysadmin ADD MEMBER <UserName>;
GO
    • Windows 認証モードの場合
      [YourDomain\UserName]を設定して実行します。
    Windows 認証ユーザー作成
    -- ユーザー作成
CREATE LOGIN [YourDomain\UserName] FROM WINDOWS;
GO
-- ユーザーにsysadminロールを設定
ALTER SERVER ROLE sysadmin ADD MEMBER [YourDomain\UserName];
GO

  3. 権限設定を確認する。
    ユーザーに設定されている権限の一覧を表示する。

    ユーザーに設定されている権限
    SELECT rp.name AS [Role], mp.name AS [Member]
FROM sys.server_role_members srm
JOIN sys.server_principals rp ON srm.role_principal_id = rp.principal_id
JOIN sys.server_principals mp ON srm.member_principal_id = mp.principal_id
GO

    ユーザーを絞り込む場合は、WHERE句に<UserName>を指定して実行します。

    ユーザーに設定されている権限（ユーザー指定）
    SELECT rp.name AS [Role], mp.name AS [Member]
FROM sys.server_role_members srm
JOIN sys.server_principals rp ON srm.role_principal_id = rp.principal_id
JOIN sys.server_principals mp ON srm.member_principal_id = mp.principal_id
WHERE mp.name = '<UserName>';
GO

    作成したユーザーにsysadminロールが設定されておけばOK。
    下記は、testUserの場合です。

    Role           Member      
-------------- ------------
sysadmin       testUser

  4. ログイン出来るか確認する。
    Ctrl +Cかexitでsqlcmdから抜けて、SQL Server 認証でログインする。
    <UserName>と<Password>を書き換えて実行する。

    SQL Server認証ログイン
    sqlcmd -S .\SQLEXPRESS -U <UserName> -P <Password>

(参考)コマンド

最低限のメモです。
GOは省略しています。

  • ユーザー一覧表示
    SELECT name FROM master.sys.syslogins;
  • saユーザー有効化
    -- パスワードを変更
ALTER LOGIN sa WITH PASSWORD = 'new_password';
-- saを有効化
ALTER LOGIN sa ENABLE;
  • データベース一覧
    SELECT name FROM sys.databases;
  • データベースの作成
    CREATE DATABASE <DataName>;
  • データベースの指定
    USE <DataName>;

Discussion