SE の雑記

SQL Server の情報をメインに Microsoft 製品の勉強内容を日々投稿

パスワードを持つ SQL ユーザーを使用した場合の別のデータベースへのクエリ実行について

leave a comment

包含データベースを使用した場合、パスワードを持つ SQL ユーザーを作成することができます。
このユーザーの場合は、ログインを持たず、データベース内に作成されたユーザーでログインが可能となります。

通常のパスワードを持つ SQL ユーザーでは、別のデータベースに対してクエリを実行することはできません。

DB を跨いだクエリを実行したい場合、包含データベースでのセキュリティのベスト プラクティス で記載されている対応を実施する必要があります。

具体的には以下のようなクエリとなります。

USE [master]
GO
ALTER DATABASE [AGDB01] SET CONTAINMENT = PARTIAL WITH NO_WAIT
GO
ALTER DATABASE [AGDB02] SET CONTAINMENT = PARTIAL WITH NO_WAIT
GO

CREATE TABLE AGDB01..T1(C1 int, C2 uniqueidentifier)
GO
CREATE TABLE AGDB02..T2(C1 int, C2 uniqueidentifier)
GO

INSERT INTO AGDB01..T1 VALUES(1, NEWID())
INSERT INTO AGDB02..T2 VALUES(1, NEWID())


USE [AGDB01]
GO
CREATE USER [CUser01] WITH PASSWORD=N'P@ssw0rd'
GO
ALTER ROLE [db_datareader] ADD MEMBER [CUser01]
GO
ALTER ROLE [db_datawriter] ADD MEMBER [CUser01]
GO
SELECT name, sid FROM AGDB01.sys.database_principals where name = 'CUser01'
GO
ALTER DATABASE AGDB01 SET TRUSTWORTHY ON
GO


USE [AGDB02]
GO
DROP USER IF EXISTS [CUser01]
GO
-- sys.database_principals で取得した SID を指定
CREATE USER [CUser01] WITH PASSWORD=N'P@ssw0rd', SID=0x010500000000000903000000BD1711B69892E645B368AFBC2ABB32EE
GO
ALTER ROLE [db_datareader] ADD MEMBER [CUser01]
GO
ALTER ROLE [db_datawriter] ADD MEMBER [CUser01]
GO

 

ポイントとしては、両データベースで、同一の SID のユーザーを作成して、最初に接続する DB に「TRUSTWORTHY」を有効にします。

これで、以下のように DB を跨いだクエリが、パスワードを持つ SQL ユーザーで実行できるようになります。

image

AlwaysOn で SQL ログインを統一する場合も SID をそろえる必要がありますが、同じ考え方ですね。

Written by masayuki.ozawa

12月 11th, 2016 at 9:27 pm

Posted in SQL Server

Tagged with

Leave a Reply

*