WebSurfer's Home

トップ > Blog 1   |   Login
Filter by APML

DB のアタッチ

by WebSurfer 13. September 2010 23:56

SQL Server の既定のインスタンスに接続するためには、まず当該 DB をデータベースエンジンにアタッチしなければなりません。無知な素人(自分のこと)は、たぶんここでハマります。また時間を無駄にすることがないように備忘録を残しておきます。

ポイントは ACL の設定です。アタッチする前に、SQL Server のサービスアカウントに、データ/ログファイル (.mdf, .ldf) があるフォルダに対するフルコントロール権限を与えることが必要です。

ACL の設定

そのことは、MSDN ライブラリ「Windows サービス アカウントの設定」(SQL Server 2005 の記事ですが、このあたりは最新版でも同じ)に書いてあったのですが、隅っこのほうに小さく書いてあって気がつくのにかなり時間がかかりました。(笑)

「SQL Server サービスに対する Windows 権限の確認」のセクションに以下のように書いてあります。

"MSSQLServer サービスの開始アカウント: アカウントは、データまたはログ ファイル (.mdf、.ndf、.ldf) が常駐するフォルダーに対するフル コントロール権限を持っている必要があります。"

なお、Express Edition の場合は、上記の MSDN ライブラリのページに書いてあるアカウント名、グループ名を以下のように読み替えてください。(下の「2013/7/1 追記」に注意)

アカウント: MSSQL$SQLEXPRESS(正確には NT SERVICE\MSSQL$SQLEXPRESS)
グループ:  SQLServerMSSQLUser$<computer_name>$SQLEXPRESS

ASP.NET 開発環境で作った mdf ファイルを App_Data フォルダごとサーバーにコピーして、それをアタッチするのであれば、App_Data フォルダへのフルアクセス権を与えなければなりません。上の画面は、その場合の設定です。

ホスティングサービスが提供する SQL Server を使う場合は知らなくて済む話ですが、自サーバーを構築しようとするとこのあたりは避けて通れません。

ただ、ホスティングサービスの場合、mdf ファイルをコピーしてサーバーに張り付けることができないところに別の苦労があるのですけどね。

------- 2013/7/1 追記 -------

SQL Server の Express 版をインストールすると、デフォルトでは「名前つきインスタンス」となり、インスタンス名は SQLEXPRESS になります。(詳しくは、別の記事 SQLEXPRESS は「名前つきインスタンス」名 を参照してください)

上記のアカウント名 MSSQL$SQLEXPRESS、グループ名 SQLServerMSSQLUser$ComputerName$SQLEXPRESS はデフォルトで SQL Server Express をインストールした場合のものです。

Express 版でも、既定のインスタンスまたは SQLEXPRESS 以外の名前つきインスタンスとしてインストールできますが、その場合は上記の名前とは異なってきますので注意してください。具体的には以下のようになるはずです。

<アカウント名>
既定のインスタンス: NT SERVICE\MSSQLSERVER
名前つきインスタンス: NT SERVICE\MSSQL$<instance_name>

<グループ名>
既定のインスタンス: SQLServerMSSQLUser$<computer_name>$MSSQLSERVER
名前つきインスタンス: SQLServerMSSQLUser$<computer_name>$<instance_name>

------- 2014/6/12 追記 -------

正しく権限が設定できていても、アタッチしたデーターベースが「読み取り専用」となることがあります。

その場合は以下のようにすると「読み取り専用」を解除することができます。

SQL Server Management Studio を起動する ⇒ オブジェクトエクスプローラに表示されている当該データベースを右クリック ⇒ 出てくるコンテキストメニューの[プロパティ(R)]をクリック ⇒ 「データーベースのプロパティ」ダイアログが表示される ⇒ 「ページの選択」ウィンドウで[オプション]を選択 ⇒ 「その他のオプション/状態/読み取り専用データベース」を False に設定(下の画像参照)

「読み取り専用」の解除

Tags: ,

SQL Server

ユーザー権限の設定

by WebSurfer 12. September 2010 20:55

開発環境でユーザーインスタンスを使って DB に接続するのではなく、DB ファイル(mdf ファイル)を SQL Server データーベースエンジンにアタッチして、それにアクセスして使えるようにするには、いろいろ面倒なことがいっぱいあります。

今回は、DB のアタッチとか、ログインユーザーの作成は済んでいるとして、ログインユーザーへの権限の与え方について備忘録を書いておきます。

ログインユーザーのサーバーロール設定

まず左の画像ですが、BGLB\SQLUser というログインユーザーへサーバーロールを与えているところです。クリックすると拡大画像が表示されます。

サーバーロールを与えるというより、デフォルトで public 権限が与えられていて、自分では何も設定していないのですが。(画像のように public にはデフォルトでチェックが入っていて、それを外そうとしても外せません)

public のサーバーレベルでの権限は、デフォルトで VIEW ANY DATABASE 権限と、既定のエンドポイント(TSQL Local Machine、TSQL Named Pipes、TSQL Default TCP、TSQL Default VIA)に対する CONNECT 権限のみです。権限は変更できるのですが、よほどの事情がない限り変更する必要はなさそうです。権限を追加したりすると、ログインユーザー全員にサーバーレベルでその権限を与えてしまうことになるので、注意が必要です。

ログインユーザーには、public の他に、dbcreator など 固定サーバーロール の権限を与えることができますが、データベースの更新、削除、実行、接続、選択、挿入をするだけの一般ユーザーに対しては、与えるサーバーロールは public のみで不足はないと思います。

ときどき、ADO.NET を使ったアプリケーションが出す "dbcreator 権限がありません" というようなエラーメッセージに惑わされ(?)て、ログインユーザーに dbcreator のセキュリティ特権を与えて(上の画像で言うと[サーバーロール (S):]下の一覧の中の dbcreator にチェックを入れて)解決したという記事を Web で目にしますが、それはやりすぎです。そのユーザーにサーバー全体で dbcreator 権限を与えてしまうことになりますので。

ログインユーザーの作り方は割愛しますが、何故 BGLB\SQLUser というドメインユーザーアカウントを使っているかについて、ちょっと説明します。

自分の試験環境ではドメインコントローラに SQL Server をインストールしていますが(サーバーを 1 台しか持っていないので、苦肉の手段です)、その場合、NETWORK SERVICE マシンアカウントを SQL Server の実行アカウントに使用できないそうです(やってみましたが、実際できませんでした)。

そういうわけで、BGLB\SQLUser というドメインユーザーアカウントを設けて、それを SQL Server の実行アカウントにしています。

また、NETWORK SERVICE は何故かログインアカウントに設定できなかったので、BGLB\SQLUser をログインアカウントにしています。Web アプリケーション(IIS のワーカープロセス)��ら SQL Server にアクセスするために、BGLB\SQLUser を偽装しています。

ユーザーマッピング

次にユーザーマッピングの設定です。「このログインにマップされたユーザー(D)」でアクセスする DB を指定します。

アクセスする DB にチェックをつけるだけで、その他はデフォルトで OK です。「UserDatabase のデータベースロールメンバーシップ(R)」もデフォルトの public のみとしておきます。

アクセス権限の細かい設定は、それぞれの DB で行います。もちろん、それぞれの DB で行うより上位のレベル(即ち、ログインアカウントでの設定)でも可能ですが、それでは権限の与えすぎになってしまうようです。

その設定は、当該 DB を右クリックして表示される「データベースのプロパティ」ダイアログで行います。次の画像を見てください。

ユーザーマッピング

「ユーザーまたはロール(U)」でアカウントを選んで(画像では BGLB\SQLUser のみですが)、「BGLB\SQLUser の権限(P)」で「明示的」タブを選択します。

そこで、更新、削除、実行、接続、選択、挿入にチェックを入れれば、ほとんどの場合 OK だと思います。

この画面は、「明示的」タブでの選択結果を「有効」タブで確認しているところです。

本当は、データを操作するのに必要なストアドを作って、ストアドに権限を設定するのがよいのだそうです(DB 全体に対する権限を与えるのではなくて)。

でも、そこまではとてもできないので、特定のアカウントに対して、特定の DB 全体を CONNECT, EXECUTE, SELECT, INSERT, DELETE, UPDATE できる 6 つの権限を与えてみました。それでは権限の与えすぎかもしれませんが。

以上、特定のデーターベースユーザーに権限を与える方法を書きましたが、データベースロールを使う方法もあります。

例えば、データベースロールの中にも public ロールがあり、これに権限を与えると当該データベースのデータベースユーザー(ログインユーザーではなくて当該データベースにマップされた特定のユーザーのみである点に注意)全員にその権限が適用されます。

それではあるユーザーに対しては権限の与えすぎになってしまう場合は、そのユーザーの権限を「拒否」して調整することができます。

その方法については、SQL Server 2012 自習書の記事 3.8 オブジェクト権限の状態(GRANT/DENY/REVOKE)が参考になると思います。

Tags:

SQL Server

PIVOT の使用

by WebSurfer 7. August 2010 18:18

SQL Server の PIVOT 関係演算子を利用してデータをまとめ、クロス集計レポートを生成し、GridView に表示する例です。

ほとんどの処置はストアドプロシージャで行うので、ASP.NET というよりは SQL Server の機能になりますが。

ここの例では、Microsoft が提供しているサンプルデータベース Northwind の Orders テーブル使用しました。

Orders テーブルは、個々の注文の ID (OrderID, int)、注文した顧客の ID (CustomerID, nchar(5))、注文を取り扱った従業員の ID (EmployeeID, int)、注文を受けた年月日 (OrderDate, datetime) などのデータを約 800 レコード持っています。

DropDownList に顧客名一覧を表示し、その中から顧客を選択すると、その注文を取り扱った従業員と取り扱った注文数を、注文年月ごとに表示するようにしました。以下のような感じです。

PIVOT によるクロス集計レポート

上記のクロス集計を行うためのストアドプロシージャは以下の通りです。

ALTER PROCEDURE dbo.StoredProcedure1
(
@CustomerID nchar(5)
)
AS
DECLARE @sqlstr nvarchar(max), @sqldata nvarchar(max), @employee nvarchar(50); 
DECLARE cur CURSOR FOR 

SELECT DISTINCT EmployeeID 
  FROM Orders 
  WHERE EmployeeID IS NOT NULL AND [CustomerID]=@CustomerID 
  ORDER BY EmployeeID; 

SET @sqldata = N''; 
SET @sqlstr = N'SELECT Year, Month '; 

OPEN cur; 
FETCH NEXT FROM cur INTO @employee; 
WHILE (@@FETCH_STATUS <> - 1) 
  BEGIN 
    IF LEN(@sqldata) > 0 
      BEGIN
        SET @sqldata = @sqldata + N',' 
      END; 
    SET @sqldata = @sqldata + N'[' + @employee + N']'
    SET @sqlstr = @sqlstr + N',[' + @employee + N'] AS [Emp-' + @employee + N']'; 
    FETCH NEXT FROM cur INTO @employee; 
  END; 
CLOSE cur;
DEALLOCATE cur; 

SET @sqlstr = @sqlstr +
  N' FROM 
     (
       SELECT DATEPART(mm, OrderDate) AS Month, DATEPART(yyyy, OrderDate) AS Year, EmployeeID 
       FROM Orders 
       WHERE CustomerID=@ID
     ) AS p 
     PIVOT 
     (
       COUNT(EmployeeID) 
       FOR EmployeeID 
       IN '; 
SET @sqlstr = @sqlstr + N'(' + @sqldata + N')) AS pvt'; 
EXECUTE sp_executesql @sqlstr, N'@ID nchar(5)', @CustomerID

顧客一覧を DropDownList に、クロス集計結果を GridView に表示するための ASP.NET のコードは以下の通りです。

<%@ Page Language="C#" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" 
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<script runat="server">

</script>

<html xmlns="http://www.w3.org/1999/xhtml">
<head id="Head1" runat="server">
  <title>無題のページ</title>
</head>
<body>
  <form id="form2" runat="server">
  <div>
    <asp:SqlDataSource ID="SqlDataSource1" runat="server" 
      ConnectionString="<%$ ConnectionStrings:Northwind %>"             
      SelectCommand="
        SELECT DISTINCT Orders.CustomerID, Customers.CompanyName 
        FROM Orders 
        INNER JOIN Customers 
        ON Orders.CustomerID = Customers.CustomerID">
    </asp:SqlDataSource>

    <asp:DropDownList ID="DropDownList1" 
      runat="server" 
      DataSourceID="SqlDataSource1" 
      DataTextField="CompanyName" 
      DataValueField="CustomerID" 
      AutoPostBack="True">
    </asp:DropDownList>
        
    <asp:SqlDataSource ID="SqlDataSource2" runat="server" 
      ConnectionString="<%$ ConnectionStrings:Northwind %>" 
      SelectCommand="StoredProcedure1"
      SelectCommandType="StoredProcedure">
      <SelectParameters>
        <asp:ControlParameter 
          ControlID="DropDownList1" 
          Name="CustomerID" 
          PropertyName="SelectedValue" 
          Type="String" />
      </SelectParameters>        
    </asp:SqlDataSource>

    <asp:GridView ID="GridView1" 
      runat="server" 
      DataSourceID="SqlDataSource2">
    </asp:GridView>
  </div>
  </form>
</body>
</html>

Tags: ,

SQL Server

About this blog

2010年5月にこのブログを立ち上げました。その後 ブログ2 を追加し、ここは ASP.NET 関係のトピックス、ブログ2はそれ以外のトピックスに分けました。

Calendar

<<  September 2021  >>
MoTuWeThFrSaSu
303112345
6789101112
13141516171819
20212223242526
27282930123
45678910

View posts in large calendar