SQL Server でデータベースに対してSQLコマンドを発行できるようになるには特別なサーバロールが付与されている場合を除き、SQL サーバへのログインユーザと各データベースのデータベースユーザを作成する必要があります。本サンプルでは、SQLサーバへのログインユーザとデータベースユーザを作成、操作する方法を記載します。

動作確認は SQL Server 2005 Developer Edition を使用しています。

1. SQLサーバのログインにWindowsユーザを追加、変更、削除

SQL Server 2000 では sp_grantlogin ストアドプロシージャを使用しますが、次のようにCREATE LOGIN SQLコマンドを使用します。

USE [master]
GO

-- SQLサーバのログインにWINDOWSユーザを追加
CREATE LOGIN [VS2K301\TestUser]
FROM WINDOWS

-- デフォルトデータベースを指定する場合
-- 既定の言語などを指定できる
CREATE LOGIN [VS2K301\TestUser]
FROM WINDOWS
WITH DEFAULT_DATABASE = [Component]

WINDOWSログインのユーザを変更する場合はALTER LOGIN SQLコマンドを使用します。

-- WINDOWSログインの変更
-- ユーザの有効、無効、
-- 既定のデータベース、言語を変更可能
ALTER LOGIN [VS2K301\TestUser] DISABLE

SQL Server にログインできるWINDOWSユーザはsys.server_principalsカタログビューから確認できます。

SELECT *
  FROM sys.server_principals
 WHERE type_desc in ('WINDOWS_LOGIN','WINDOWS_GROUP')

WINDOWSユーザをログインユーザから削除する場合は DROP LOGIN コマンドを使用します。

DROP LOGIN [VS2K301\TestUser]

特定のWINDOWSユーザ、グループからアクセスを許可、拒否する場合はDENY CONNECT SQL, GRANT CONNECT SQL を使用します。

-- 特定のWindowsのユーザ、グループからのアクセスを
-- 拒否する場合
DENY CONNECT SQL TO [VS2K301\TestUser]
-- 許可する場合
GRANT CONNECT SQL TO [VS2K301\TestUser]

2. SQLサーバのユーザを作成、変更、削除する

SQLサーバレベルで管理するSQLユーザもWindowsユーザをログインユーザとする場合とほぼ同様に追加、変更、削除できます。

-- SQLサーバが管理するログインユーザを作成
CREATE LOGIN [TestSqlUser]
WITH PASSWORD = 'Password'

-- 既定のデータベースを指定する場合
CREATE LOGIN [TestSqlUser]
WITH PASSWORD = 'Password'
    ,DEFAULT_DATABASE = [Component]

-- 削除はWindowsログインの場合と同様
DROP LOGIN [TestSqlUser]

SQL Serverユーザのログイン情報もsys.server_principals セキュリティーカタログビューから確認できます。

Books Online を確認すると、SQL サーバユーザの詳細な作成オプション(複雑性、有効期限など)を確認できます。

3. サーバロールへの追加、削除

SQLログインに追加したWindiws,SQL Server ユーザにサーバロールを付与ふるには sp_addsvrrolemember , sp_dropsrvrolemember を使用します。サンプルではSQLログインユーザのTestSqlUserにsysadminロールを付与しています。

-- SQLユーザTestSqlUserにsysadminサーバロールを追加
EXEC master.sys.sp_addsrvrole 'TestSqlUser', 'sysadmin'

-- SQLユーザTestSqlUserにsysadminサーバロールを削除
EXEC master.sys.sp_dropsrvrolemember 'TestSqlUser', 'sysadmin'

サーバロール情報を表示する場合はsys.server_principalsセキュリティカタログビューやsp_helpsrvroleストアドプロシージャを使用します。

-- Serverロールを取得する
SELECT *
  FROM sys.server_principals
 WHERE type_desc = 'SERVER_ROLE'

-- サーバロール一覧を列挙する,publicは一覧に表示されない
EXEC master.sys.sp_helpsrvrole

特定のサーバロールを付与されているメンバを取得する場合は sp_helpsrvrolemember ストアドプロシージャを使用します。

-- サーバロールに含まれるメンバーを取得する
EXEC master.sys.sp_helpsrvrolemember
-- 特定のサーバロールのメンバーを取得する
EXEC master.sys.sp_helpsrvrolemember @srvrolename = 'sysadmin'

4. データベースユーザを作成する

データベースユーザは作成したSQLサーバのログインユーザをデータベースユーザにマッピングを行うことで作成します。サンプルではWindowsユーザのログインを作成し、そのユーザTestUserに対してAdventureWorksDWデータベースにCREATE USER コマンドを使用してデータベースユーザを指定します。Books Online を参照すると、デフォルトスキーマの指定や証明書の設定を行えます。

-- 1.データベースユーザを作成する。
USE [master]
GO

CREATE LOGIN [VS2K301\TestUser]
FROM WINDOWS
WITH DEFAULT_DATABASE = [AdventureWorksDW]

DROP LOGIN [VS2K301\TestUser]

-- 2.データベースAdventureWorksDW
--   データベースユーザを作成
USE [AdventureWorksDW]
GO
CREATE USER [VS2K301\TestUser]

FOR LOGINを指定することで、SQLのログインユーザとは別名のデータベースユーザ名でデータベースユーザを作成できます。下のサンプルではログインユーザVS2K301\TestUserに対してデータベースユーザをOtherNameという名前で作成しています。

CREATE USER [OtherName]
 FOR LOGIN [VS2K301\TestUser]

データベースユーザを削除するにはDROP文を使用します。例では先ほど作成したデータベースユーザOtherNameを削除しています。

-- データベースユーザを削除する
DROP USER [OtherName]

ユーザ情報を変更する場合はALTER USERコマンドを使用します。

-- データベースユーザ名を変更
ALTER USER [OtherName]
 WITH NAME = [OtherName2]

データベースユーザ情報(ログイン名やグループ名)を表示する場合は sp_helpuser システムストアドプロシージャを使用します。

-- データベースユーザ情報を表示
EXEC sp_helpuser @name_in_db = 'OtherName2'

パラメタを指定せずに呼び出すとデータベースのすべてのユーザ情報が表示されます。

ログインユーザとDBユーザのマッピングを表示するクエリは次のようになります。

select s.name LoginName, d.name DBName, d.sid
  from sys.database_principals d
inner join sys.server_principals s
    on d.sid = s.sid

- データベースユーザとSQL Serverログインユーザのマッピングが失われた場合
バックアップしたデータベースを別のデータベースやデータベースサーバのインスタンスにリストアするデータベースユーザとSQLログインユーザのマッピングが失われます。データベースユーザとSQLサーバのログインユーザのマッピングを修正する場合は sp_change_users_login ストアドプロシージャを使用します。パラメタの@Actionの値にReportやauto_fix,update_oneを指定することでマッピングの失われたユーザのレポートや修正を行えます。

固定データベースロール情報の表示する場合はsp_helpdbfixedroleストアドプロシージャを使用します。

EXEC sp_helpdbfixedrole

sp_helprolememberストアドを使用すると固定データベースロールに割り当てられたユーザ一覧を取得できます。

5. データベースロールメンバシップの付与と削除

データベースロールにデータベースユーザやロールを付与するにはsp_addrolemember,sp_droprolememberを使用します。例ではデータベースユーザTestSqlUserにdb_ownerデータベースロールを付与、削除しています。

sp_addrolemember 'db_owner', 'TestSqlUser'
sp_droprolemember 'db_owner', 'TestSqlUser'

ユーザ定義のデータベースロールを作成、修正、削除するにはCRATE ROLE, ALTER ROLE, DROP ROLE コマンドを使用します。データベースロールへのユーザの追加削除は、sp_addrolemember,sp_droprolememberを使用できます。追加されたロール情報は sp_helprole ストアドプロシージャで確認できます。

EXEC sp_helprole

作成したロールにGRANT,DENYコマンドを使用して必要な権限を持つロールを作成して管理しやすいロールを作成します。

データベースロールの作成や修正派CREATE ROLE [role_name],ALTER ROLEを使用します。ロールに対してGRANTをすることで、権限の設定を行えるようになります。

 6.アプリケーションロールの管理

アプリケーションロールを使用すると、sp_setapproleストアドプロシージャを使用してアプリケーションロールを活性化した場合、そのセッション中にsp_setapproleで指定したアプリケーションロールの権限でクエリを実行するようになります。そのため、アプリケーションロールに権限を付与して始めて効果があります。

アプリケーションロールの作成、修正、削除はCREATE APPLICATION ROLE, ALTER APPLICATION ROLE, DROP APPLICATION ROLE を使用します。アプリケーションロールを作成した後、GTANTコマンドで権限をアプリケーションロールに設定します。

sp_setapproleにアプリケーションロール名とCREATE APPLICATION ROLE で指定したパスワードを使用してアプリケーションロールを活性化することで、同じセッション中にアプリケーションロールの権限でクエリを使用するようにできます。

7. おわり

説明は以上です。間違い、誤り等がありましたらご連絡ください。