none
サーバーアクセスの権限付与について RRS feed

  • 質問

  • 皆様、こんばんわ!

    SQL Serverの権限設定について質問をさせて下さい。

    まだ詳細について詳しい事は何も分からず、簡単な入門書と実際の画面を見ながら
    試している段階で、以下の記述そのものが曖昧で間違った認識の所も在るかと思い
    ますが、それぞれご指摘頂けると有り難いです。

    以下、宜しくお願いします。


    特定の DB使ったアプリの開発を目的として、これを自由に扱えるようにと考え目的の
    ドメインアカウントを SQL Server で以下のように設定しています。

     1.サーバー → セキュリティー → ログイン に該当アカウントを登録し、
       サーバーロールは【public】にチェック

     2.次に目的の DBのセキュリティー → ユーザー に該当アカウントを登録し、
       ロールメンバーは【db_owner】にチェック

    この状態で、該当アカウントで 特定の DBにおいて、DBファイルをデタッチする
    事ができます。 しかし、一旦デタッチすると、再びこの DBファイルをアタッチ
    して DBを作る事ができません。

    考えてみれば、特定の DBを扱う事ができるアカウントなので、存在しない DBを
    作る事ができなくて当然と考えられます。

    しかし、SQL Serverを使ったアプリを作ろうと思えば、作業をやり易くするため
    自分で自由にDBを作れたらと考え、この場合【dbcreator】辺りが必要かなと
    思いここにチェックを入れてみましたが様子は変わりません。


    まずは、自分の DBファイルのアタッチ/デタッチは自由にできて、他のDBに対し
    てはアクセス禁止とするには、どんな設定になるのでしょうか。

    またこの辺りを旨くまとめた資料など、教えて頂けると嬉しいです。

    どうか宜しくお願い致します。

    2011年4月15日 9:40

回答

  • ファイルのアタッチですが、[CREATE DATABASE] の権限が必要となります。

    クエリで権限を付与する場合は以下のようになります。
    CREATE DATABASE に関しては、サーバーレベルの権限となりますので、対象のログインに対して権限を付与する必要があります。

    GRANT CREATE DATABASE TO [WIN-1QM471JPL04\attachuser]
    

    dbcreator には上記の権限が付与されているのですが、今回お使いの Windows 認証の場合、対象のログインにアタッチ対象のファイルの権限としてフルコントロールが付与されていないと以下のエラーとなります。

    Msg 5120, Level 16, State 101, Line 1
    物理ファイル "F:\ATTACH.mdf" を開けません。オペレーティング システム エラー 5: "5(アクセスが拒否されました。)"。

    この場合は、 SQL Server の権限ではなく、アタッチ先のファイルに対して適切なアクセス権が設定されていない状態となっていますので .mdf / .ldf にアタッチを実行するユーザーのフルコントロールを付与していただくと解消するかと思います。

     

    デタッチの場合はデータベースに対する操作になりますので対象のデータベースに対して [db_owner] の権限が必要となります。

    クエリで権限を付与する場合は以下のようになります。
    db_owner の権限に関してはデータベースレベルの権限となりますので、データベースのユーザーに対して権限を付与する必要があります。

    USE [ATTACH_DB]
    GO
    CREATE USER [WIN-1QM471JPL04\attachuser] FOR LOGIN [WIN-1QM471JPL04\attachuser]
    GO
    USE [ATTACH_DB]
    GO
    ALTER ROLE [db_owner] ADD MEMBER [WIN-1QM471JPL04\attachuser]
    GO
    
    

    上記のクエリでは、attachuser という Windows ユーザーで [ATTACH_DB] に対してユーザーを作成して、作成したユーザーに対して [db_owner] の権限を付与しています。

    • アタッチできるデータベースを限定する場合は、ファイルのアクセス権で制御
    • デタッチできるデータベースを限定する場合は、ユーザーの権限で制御

    を用いることで今回の要件を満たすことはできませんでしょうか?

    必要なる権限の詳細に関しては以下の技術情報に記載されています。

    [アタッチ]
    CREATE DATABASE (Transact-SQL)

    [デタッチ]
    sp_detach_db (Transact-SQL)
     

    それぞれの技術情報で [権限] の項目を見ていただくと必要となる権限が記載されています。

    以上、ご参考になれば幸いです。

    2011年4月15日 15:16
    モデレータ
  • >---------------------------------------------------------------
    > 要求されたダイアログを表示できません。
    > 追加情報:

    >  パラメータ名:nColIndex
    >  実際の値は -1 です。(Microsoft.SqlServer.GridControl)
    >---------------------------------------------------------------

    ですが、私の環境でも再現いたしました。

    SQL Server Management Studio (SSMS) で GUI を使用してアタッチをしようとした場合、[dbcreator] の権限だけでは上記のエラーが発生してしまうようです。
    SSMS の GUI でアタッチする際には、内部で [xp_regread] というレジストリを参照する拡張ストアドプロシージャが実行されているのですが、[sysadmin] の権限がないと使用しているレジストリにアクセスができずにエラーとなってしまっているようでした。
    # [管理者として実行] をしてもエラーは解消しませんでした。

    関連する技術情報としては以下の情報がありました。
    Actual value was -1. (Microsoft.SqlServer.GridControl) Error when attaching database
    SQL Server Management studio - cannot show requested dialog NColIndex
    SQL management Studio 2008
    SQL Server : Droits pour lire et écrire dans la base de registre
    SQL Server : Déterminer où se trouvent les répertoires par défaut via TSQL
    [BUG] SQL Server 2000 Service Pack 4 に、SQL Server 2000 からアップグレードした後、クエリが拡張ストアド プロシージャを呼び出すと、アクセスが拒否されました"というエラー メッセージが表示される場合があります。
    # 私の環境でSQL Server 2008 R2 でレジストリを変更してみたのですがうまく動作させることができませんでした。

    [dbcreator] の権限だけでアタッチする場合には、SSMS の GUI からではなく、クエリを実行してアタッチしないとだめかもしれません…。

     >固定サーバーロール名:Database Creators(短縮名:dbcreator)は、仰っている
     >[CREATE DATABASE] の権限 とは異なるのでしょうか

    [dbcreator] は [CREATE DATABASE] 権限を持っているユーザーになりますので、厳密にいうと [CREATE DATABASE] 権限とは異なります。

    sp_srvrolepermission 'dbcreator'
    

    を実行していただくとどのような権限が付与されているか確認することができます。

    • ALTER DATABASE
    • Add member to dbcreator
    • CREATE DATABASE
    • DROP DATABASE
    • Extend database
    • RESTORE DATABASE
    • RESTORE LOG
    • sp_renamedb

    といった権限が付与されているロールが [dbcreator] となります。
    # CREATE DATABASE 以外の権限も付与されています。

    > 先に書いて頂いた  [WIN-1QM471JPL04\attachuser] の意味を調べているのですが、
    > よく分かりません。
    > ヒントを頂ければ有り難いのですが、宜しくお願いします

    [WIN-1QM471JPL04\attachuser]  ですが私の使っている環境に存在している Windows のユーザーとなります。
    [WIN-1QM471JPL04] というコンピューター名の [attachuser] という Windows のユーザーに対して権限を付与するコマンドの例として記載させていただきました。

    この箇所に関しては利用されているユーザー名に読み替えていただけますでしょうか。

    以上、ご参考になれば幸いです。

    2011年4月18日 13:55
    モデレータ
  • そこで、[dbcreator] と [CREATE DATABASE]ですが、ロール [dbcreator]は
    権限CREATE DATABASE を含み、それ以外の権限も付与されているので、ログ
    インが[dbcreator]のメンバーであれば、 T-SQLを使ってアタッチ可能との
    認識で間違いないか確認させてください。(ファイルのアクセス権付ありが前提

    はい、その認識で問題ありません。

    T-SQL で実行する場合もログインに必要な権限を付与している必要があります。

    2011年4月19日 11:02
    モデレータ
  • 自分がアタッチ時した DB については、[db_owner] を動的に付与するということはできないと思います。
    データベースのユーザーの権限は、アタッチ前の状態をそのまま引き継ぎます。

    今回、考えられていることを実現するためには、[sysadmin] の権限を付与しているログインで SQL Server にログインし、対象のユーザーに [db_owner]の権限を付与するという運用が必要になるかと。 


    先頭に N がついているのは文字列を Unicode 文字列として認識させるためのプレフィックスとなります。
    SQL Server で Unicode 文字列定数を処理するときは、すべての Unicode 文字列の前に N プレフィックスを付ける必要がある

     

    2011年4月20日 8:58
    モデレータ

すべての返信

  • ファイルのアタッチですが、[CREATE DATABASE] の権限が必要となります。

    クエリで権限を付与する場合は以下のようになります。
    CREATE DATABASE に関しては、サーバーレベルの権限となりますので、対象のログインに対して権限を付与する必要があります。

    GRANT CREATE DATABASE TO [WIN-1QM471JPL04\attachuser]
    

    dbcreator には上記の権限が付与されているのですが、今回お使いの Windows 認証の場合、対象のログインにアタッチ対象のファイルの権限としてフルコントロールが付与されていないと以下のエラーとなります。

    Msg 5120, Level 16, State 101, Line 1
    物理ファイル "F:\ATTACH.mdf" を開けません。オペレーティング システム エラー 5: "5(アクセスが拒否されました。)"。

    この場合は、 SQL Server の権限ではなく、アタッチ先のファイルに対して適切なアクセス権が設定されていない状態となっていますので .mdf / .ldf にアタッチを実行するユーザーのフルコントロールを付与していただくと解消するかと思います。

     

    デタッチの場合はデータベースに対する操作になりますので対象のデータベースに対して [db_owner] の権限が必要となります。

    クエリで権限を付与する場合は以下のようになります。
    db_owner の権限に関してはデータベースレベルの権限となりますので、データベースのユーザーに対して権限を付与する必要があります。

    USE [ATTACH_DB]
    GO
    CREATE USER [WIN-1QM471JPL04\attachuser] FOR LOGIN [WIN-1QM471JPL04\attachuser]
    GO
    USE [ATTACH_DB]
    GO
    ALTER ROLE [db_owner] ADD MEMBER [WIN-1QM471JPL04\attachuser]
    GO
    
    

    上記のクエリでは、attachuser という Windows ユーザーで [ATTACH_DB] に対してユーザーを作成して、作成したユーザーに対して [db_owner] の権限を付与しています。

    • アタッチできるデータベースを限定する場合は、ファイルのアクセス権で制御
    • デタッチできるデータベースを限定する場合は、ユーザーの権限で制御

    を用いることで今回の要件を満たすことはできませんでしょうか?

    必要なる権限の詳細に関しては以下の技術情報に記載されています。

    [アタッチ]
    CREATE DATABASE (Transact-SQL)

    [デタッチ]
    sp_detach_db (Transact-SQL)
     

    それぞれの技術情報で [権限] の項目を見ていただくと必要となる権限が記載されています。

    以上、ご参考になれば幸いです。

    2011年4月15日 15:16
    モデレータ
  • Masayuki.Ozawa さん、こんばんわ。

    早々に詳しいご説明を頂き、有難うございます。

    クエリで権限を扱う方法をお示し頂きましたが、実は私、SQL Server Management Studioの
    オブジェクト エクスプローラーしか使った経験が無く、T-SQLの資料を参考に、以下のように
    理解しました。

    1.対象のログインに対して【dbcreator】
    2.アタッチの対象となるファイルに、対象ユーザーの【フルコントロール】
    3.対象データベースの該当ユーザーに【db_owner】

    今、確認できる環境が手元にないため、月曜日以降に確認させて頂きます。

    どうも、有難うございました。

    2011年4月16日 16:39
  • Masayuki.Ozawa さん、お世話になります。

    早速 試してみましたが不明な箇所がありますので、再度質問させて下さい。。
    まず、サーバーレベルでの問題に絞り込んでお尋ねします。

    アタッチのためにサーバレベルの権限として、対象ログインのサーバーロールに
    【dbcreatot】をセットしてみました。

    この状態で、「データベース」右クリックで【新しいデータベース(N)...】で
    DBを作る事はできるようになりました。
    しかし、「データベース」右クリックで【アタッチ(A)...】をクリックすると、
    以下のダイアログが表示されてしまいます。

    ---------------------------------------------------------------
     要求されたダイアログを表示できません。
     追加情報:

      パラメータ名:nColIndex
      実際の値は -1 です。(Microsoft.SqlServer.GridControl)
    ---------------------------------------------------------------

    もちろん、管理者権限を持ったログインで行うと「データベースのアタッチ」
    ダイアログが表示されます。

    固定サーバーロール名:Database Creators(短縮名:dbcreator)は、仰っている
    [CREATE DATABASE] の権限 とは異なるのでしょうか。

    それとも、まだ何かセットする箇所が漏れているのでしょうか。

    どうぞ、宜しくお願いします。


    P.S.
     先に書いて頂いた  [WIN-1QM471JPL04\attachuser] の意味を調べているのですが、
     よく分かりません。
     ヒントを頂ければ有り難いのですが、宜しくお願いします。

    2011年4月18日 2:14
  • >---------------------------------------------------------------
    > 要求されたダイアログを表示できません。
    > 追加情報:

    >  パラメータ名:nColIndex
    >  実際の値は -1 です。(Microsoft.SqlServer.GridControl)
    >---------------------------------------------------------------

    ですが、私の環境でも再現いたしました。

    SQL Server Management Studio (SSMS) で GUI を使用してアタッチをしようとした場合、[dbcreator] の権限だけでは上記のエラーが発生してしまうようです。
    SSMS の GUI でアタッチする際には、内部で [xp_regread] というレジストリを参照する拡張ストアドプロシージャが実行されているのですが、[sysadmin] の権限がないと使用しているレジストリにアクセスができずにエラーとなってしまっているようでした。
    # [管理者として実行] をしてもエラーは解消しませんでした。

    関連する技術情報としては以下の情報がありました。
    Actual value was -1. (Microsoft.SqlServer.GridControl) Error when attaching database
    SQL Server Management studio - cannot show requested dialog NColIndex
    SQL management Studio 2008
    SQL Server : Droits pour lire et écrire dans la base de registre
    SQL Server : Déterminer où se trouvent les répertoires par défaut via TSQL
    [BUG] SQL Server 2000 Service Pack 4 に、SQL Server 2000 からアップグレードした後、クエリが拡張ストアド プロシージャを呼び出すと、アクセスが拒否されました"というエラー メッセージが表示される場合があります。
    # 私の環境でSQL Server 2008 R2 でレジストリを変更してみたのですがうまく動作させることができませんでした。

    [dbcreator] の権限だけでアタッチする場合には、SSMS の GUI からではなく、クエリを実行してアタッチしないとだめかもしれません…。

     >固定サーバーロール名:Database Creators(短縮名:dbcreator)は、仰っている
     >[CREATE DATABASE] の権限 とは異なるのでしょうか

    [dbcreator] は [CREATE DATABASE] 権限を持っているユーザーになりますので、厳密にいうと [CREATE DATABASE] 権限とは異なります。

    sp_srvrolepermission 'dbcreator'
    

    を実行していただくとどのような権限が付与されているか確認することができます。

    • ALTER DATABASE
    • Add member to dbcreator
    • CREATE DATABASE
    • DROP DATABASE
    • Extend database
    • RESTORE DATABASE
    • RESTORE LOG
    • sp_renamedb

    といった権限が付与されているロールが [dbcreator] となります。
    # CREATE DATABASE 以外の権限も付与されています。

    > 先に書いて頂いた  [WIN-1QM471JPL04\attachuser] の意味を調べているのですが、
    > よく分かりません。
    > ヒントを頂ければ有り難いのですが、宜しくお願いします

    [WIN-1QM471JPL04\attachuser]  ですが私の使っている環境に存在している Windows のユーザーとなります。
    [WIN-1QM471JPL04] というコンピューター名の [attachuser] という Windows のユーザーに対して権限を付与するコマンドの例として記載させていただきました。

    この箇所に関しては利用されているユーザー名に読み替えていただけますでしょうか。

    以上、ご参考になれば幸いです。

    2011年4月18日 13:55
    モデレータ
  • Masayuki.Ozawa さん、こんにちわ。

    お示し頂いた資料を見ますと、バグっぽいやり取りがされているようで
    SQL management Studio 2008 がドンピシャのように思えます。
    (英文殆ど理解できていませんが・・・)

    そこにある

     The workaround for this currently is to use T-SQL to attach database .

    が決め手で、まずは  T-SQL の構文から勉強してみたいと思います。


    所で、 T-SQLを使ってアタッチする場合でもログインに必要な権限が与え
    られている事が条件になるのは当然ですね。

    そこで、[dbcreator] と [CREATE DATABASE]ですが、ロール [dbcreator]は
    権限CREATE DATABASE を含み、それ以外の権限も付与されているので、ログ
    インが[dbcreator]のメンバーであれば、 T-SQLを使ってアタッチ可能との
    認識で間違いないか確認させてください。(ファイルのアクセス権付ありが前提)

    再度トライしてみますので、よろしくお願いします。

    2011年4月19日 5:10
  • そこで、[dbcreator] と [CREATE DATABASE]ですが、ロール [dbcreator]は
    権限CREATE DATABASE を含み、それ以外の権限も付与されているので、ログ
    インが[dbcreator]のメンバーであれば、 T-SQLを使ってアタッチ可能との
    認識で間違いないか確認させてください。(ファイルのアクセス権付ありが前提

    はい、その認識で問題ありません。

    T-SQL で実行する場合もログインに必要な権限を付与している必要があります。

    2011年4月19日 11:02
    モデレータ
  • Masayuki.Ozawa さん、こんにちわ。

    何度も恐れ入ります。


    一歩進んだのですが、次の問題で行き詰まっています。
    以下、ログイン testuser にサーバーロール【dbcreator】を設定した状態で記しています。

    create database [test_db] on (filename =  'd:\test.mdf' ), ( FILENAME = 'd:\test_log.ldf' ) FOR ATTACH

    でアタッチできることが確認できました。(SSMSで GUI を使用してのアタッチはエラーで不可です)

    次の段階として、この状態では testuser に test_db アクセス権限がないため、db_owner にしたいと考えますが、

    exec sp_addrolemember 'db_owner', 'KBS-KYOTO\sqluser' では、

    「ユーザーにはこの操作を実行する権限がありません。」となり先に進めません。

    たぶん、アタッチする際に自分をそのオーナーにする方法があるのかなと思い調べてみましたが詳細がわかりません。
    自分でアタッチしたDB に限り、sysadmin を与えずに db_owner にできるようにするには、どのような権限の与え方・
    記述を行えば良いのでしょうか。、


    もう一つ、基本的なことかと思いますが、アタッチのための構文の filename 指定で、

    FILENAME = N'C:\Program ・・・ のように頭に "N" が付いていますが、この意味は何なのでしょうか。

    どうぞよろしくお願いいたします。

    2011年4月20日 7:59
  • 自分がアタッチ時した DB については、[db_owner] を動的に付与するということはできないと思います。
    データベースのユーザーの権限は、アタッチ前の状態をそのまま引き継ぎます。

    今回、考えられていることを実現するためには、[sysadmin] の権限を付与しているログインで SQL Server にログインし、対象のユーザーに [db_owner]の権限を付与するという運用が必要になるかと。 


    先頭に N がついているのは文字列を Unicode 文字列として認識させるためのプレフィックスとなります。
    SQL Server で Unicode 文字列定数を処理するときは、すべての Unicode 文字列の前に N プレフィックスを付ける必要がある

     

    2011年4月20日 8:58
    モデレータ
  • Masayuki.Ozawa さん、こんにちわ。

    何かと、お世話になっております。

    > データベースのユーザーの権限は、アタッチ前の状態をそのまま引き継ぎます。

    この件、確認させて頂きました。

    仰っている通り、、[sysadmin] の権限を付与しているログインを使って、対象DBの各種権限を
    それぞれのユーザーに合わせて設定した上で、各ユーザーに利用してもらう事にします。

    色々と教えて頂き、当初やろうとしていた事ができるようになりました。
    どうも、有り難うございました。


    所で、今回色々と動きを確かめている中で、新たな質問が出てきましたが、別件と言う事で改めて
    別のスレッドを立てさせて頂こうかと思っています。
    今後とも、どうぞ宜しくお願いします。

    これを持ちまして、このスレッドはクローズさせて頂きます。

    2011年4月25日 5:17