SQL Server：データベースユーザーのデフォルトスキーマを切り替えてみよう！

UPDATE [{schema}].[TestMaster]
SET
  ID = @id,
  NAME = @name;

普段実装する上でクエリのスキーマについてあまり意識していなかったので、この際に調べてみようと思いました。

スキーマとは

まず、DBにおけるスキーマとはどのようなものをいうのでしょうか。wikiによると、以下のように記載があります。

データベースの構造であり、データベース管理システム (DBMS) でサポートされている形式言語で記述される。

つまり、DBの設計書のようなものと考えることが出来ます。

検証用DBの作成

今回はローカルで検証用のDBを作成してテスト用ユーザーとスキーマの作成を試そうと思います。SQLServerのインストーラーでローカルにインストールしてしまうと検証後の削除が大変なので、Dockerで環境を作りたいと思います。作成方法はMSの公式サイトを参考にしました。

DockerイメージのPullとコンテナの実行

公式のSQL Server Linux コンテナイメージをpullします。

sudo docker pull mcr.microsoft.com/mssql/server:2022-latest

sudo docker run -e "ACCEPT_EULA=Y" -e "MSSQL_SA_PASSWORD=XXXXX" \
-p 1433:1433 --name sql1 --hostname sql1 \
-d \
mcr.microsoft.com/mssql/server:2022-latest

docker ps

ip a | grep eth0 | grep inet

CREATE DATABASE TestDB;

ALTER LOGIN sa WITH DEFAULT_DATABASE=TestDB;

-- SQL Serverログイン作成
CREATE LOGIN test_user WITH PASSWORD = 'test_U$er1234', DEFAULT_DATABASE = TestDB;
-- データベースユーザー作成
CREATE USER test_user FOR LOGIN test_user;

CREATE SCHEMA test;

CREATE TABLE dbo.Users(
id INT IDENTITY(1,1) PRIMARY KEY,
name NVARCHAR(50)
);
CREATE TABLE test.Users(
id INT IDENTITY(1,1) PRIMARY KEY,
name NVARCHAR(50)
);

INSERT INTO Users(name) VALUES ('Alice');
INSERT INTO Users(name) VALUES ('Bob');
INSERT INTO Users(name) VALUES ('Charlie');
INSERT INTO Users(name) VALUES ('David');
INSERT INTO Users(name) VALUES ('Eve');
INSERT INTO Users(name) VALUES ('Frank');
INSERT INTO Users(name) VALUES ('Grace');
INSERT INTO Users(name) VALUES ('Helen');
INSERT INTO Users(name) VALUES ('Ivan');
INSERT INTO Users(name) VALUES ('Jack');

SELECT * FROM dbo.Users;

SELECT * FROM test.Users;

ALTER USER test_user WITH DEFAULT_SCHEMA = test;

CREATE LOGIN test2 WITH PASSWORD = 'test_U$er1234'; 
USE TestDB;
CREATE USER test2 FOR LOGIN test2 WITH DEFAULT_SCHEMA = test;

DENY CONTROL ON SCHEMA :: dbo TO test_user;
GRANT CONTROL ON SCHEMA :: test TO test_user;

SELECT * FROM dbo.Users;

SELECT * FROM test.Users;