SQL server認証に必要な最低限のロールを教えてください。

質問

こんにちは、いつも参考にさせて頂いております。

SQL Server2008でDBを管理しているのですが、
あるインスタンスへSQL server認証ができません。

sysadmin権限をつけたら認証されたのですが、できるだけ権限は最小にとどめたいです。

そこで質問です。
SQL server認証に必要な最小のサーバー ロールとデータベースのロールは何でしょうか？
アドバイスやヒントなど、何かお気づきでしたら教えてください。
どうか、宜しくお願いいたします。

[現在の設定]
サーバー ロール：public

ユーザーマッピング：
何も設定していません。
設定しようとDBにチェックすると、ユーザーにログインユーザーと同じ名が入り、
既に存在しています、と怒られてしまいます。
ユーザー名に違う名前（例えばdbo）を設定しても同様に怒られました。
なので、ここの設定は何もしないのが正しいのかな？と思っていますが、
根拠はありません・・

[環境]
SQL Server2008
Windows Server2008

回答No.1

jamshid6 · ベストアンサー

全くの推測ですが、そのデータベースはリストアしたものでしょうか？

データベースの中の方のセキュリティを開いて、該当ユーザを一旦削除し、それからやり直してみてください。
なお、接続するだけなら既定のデータベースがmasterになっている限りはPublicのみでOKのはずです。

補足

jamshid6さん、ご回答ありがとうございます。

>全くの推測ですが、そのデータベースはリストアしたものでしょうか？
その通りです。びっくりして一瞬止まってしまいました。

データベースの中の方のセキュリティを開いて、該当ユーザを一旦削除し、それからやり直してみてください。
なお、接続するだけなら既定のデータベースがmasterになっている限りはPublicのみでOKのはずです。
なるほどです。やはり、Publicだけで接続できないとおかしいですよね。

これから試してみたいのですが、もしお時間が許せばあと3点ほど質問にお付き合いいただけないでしょうか。
(1)上記の接続、というのはレコードの挿入や更新も含まれますでしょうか？
(2)ユーザーマッピングの設定は対象DBに対して以下で大丈夫でしょうか？
db_datareader,db_datawriter,db_ddladmin,public
(3)この原因で何か思い当たることがあれば教えていただけないでしょうか。

長々とすみませんが、どうか宜しくお願いいたします。

回答No.3

3rd_001

リストアしたということは、もしかして以下の問題でしょうか？

http://www.microsoft.com/japan/sqlserver/2005/ssj/tips/03.mspx

リストアでログインのマッピングが切れるのはよくあるので注意が必要です。

補足

3rd_001さん、ご回答ありがとうございます。

非常にわかりやすいサイトですね…。
これを早く見つけたかったです。

>リストアでログインのマッピングが切れるのはよくあるので注意が必要です。
よくあることだとは・・・。
でも、自分がやらかしたと思っていたので少しほっとしました。
（やらかしたのに変わりはありませんが）
ありがとうございました。

回答No.2

jamshid6

>(1)上記の接続、というのはレコードの挿入や更新も含まれますでしょうか？
接続には更新のみならず参照も含まれません。

>(2)ユーザーマッピングの設定は対象DBに対して以下で大丈夫でしょうか？
>db_datareader,db_datawriter,db_ddladmin,public
ロールについてはDDL権限も与えるならば、その４つのロールで足りるでしょう。

>(3)この原因で何か思い当たることがあれば教えていただけないでしょうか。
他の環境からリストアしたデータベースの場合、元の環境のユーザは内部管理レベルでは今の環境のユーザとは同じものとみなされないため、
「インスタンスレベルでは割り当てられないように見えるがデータベースレベルでは存在する」という事象がおこります。
でもユーザ名は同じなので、割当てようとすると「既に存在します」とエラーになります。
やはり一旦データベースレベルのユーザは削除し、インスタンスレベルで割り当て直すのが一般的かと思います。

補足

jamshid6さん、ご回答ありがとうございます。

>(1)上記の接続、というのはレコードの挿入や更新も含まれますでしょうか？
>接続には更新のみならず参照も含まれません。
>(2)ユーザーマッピングの設定は対象DBに対して以下で大丈夫でしょうか？
>db_datareader,db_datawriter,db_ddladmin,public
>ロールについてはDDL権限も与えるならば、
>その４つのロールで足りるでしょう。
不安を解消していただきありがとうございます。DDL権限も欲しいので
サーバーにpublic＋DBに上記4ロールでいこうと思います。

>(3)この原因で何か思い当たることがあれば教えていただけないでしょうか。
> 他の環境からリストアしたデータベースの場合、
>元の環境のユーザは内部管理レベルでは今の環境のユーザとは
>同じものとみなされないため、
> 「インスタンスレベルでは割り当てられないように見えるが
>データベースレベルでは存在する」という事象がおこります。
> でもユーザ名は同じなので、割当てようとすると
>「既に存在します」とエラーになります。

そういうことがあるのですね、勉強になります。

また、情報を頼りにサポート情報をみつけたので、早速確認してみたら…
「対応するログインの存在しないユーザー」
http://support.microsoft.com/kb/274188/

USE データベース名
EXEC sp_change_users_login 'Report'
⇒結果：作成したユーザーが見事にひっかかりました。

> やはり一旦データベースレベルのユーザは削除し、
>インスタンスレベルで割り当て直すのが一般的かと思います。
そうさせていただきます。
今回もまた助けていただきありがとうございました。