ユーザーに与える権限に付いて

SQL Server2008でDBを管理しているのですが、
エンドユーザーに与える権限に付いて教えて下さい。

ACCESS ADPから接続してデータの登録、更新、削除を行ないます。

現在、ログインでsysadminをチェックしないと上記の作業が出来ません。

データベースユーザー全てのロールをチェックしてもログインで
sysadminがチェックしてないと動作しません。

エンドユーザーのログインではsysadminをチェックしない方がいいと
考えるのですが、その場合各データベースのセキュリティはどのように
設定したらいいのでしょうか?

No.1 ベストアンサー 回答者： jamshid6 回答日時： 2009/11/17 00:32

>データベースユーザー全てのロールをチェックしてもログインで

sysadminがチェックしてないと動作しません。

全部？全部ってdenydatareaderもですか？それは動作しないです。
（GRANTよりDENYが優先されます）

テーブル・ビューの参照・更新しか行わないのであれば、public/datareader/datawriterだけつけるのがよいでしょう。
この場合、ストアドプロシージャの実行はできませんのでストアドプロシージャ単位に個別にEXECUTE権限を付与することになります。

それの管理が面倒くさくてdb_ownerを付与するケースもままあります。
いずれもしてもsysadminは必要ありません。

この回答へのお礼

いつもありがとうございます。

やはり、ストアド単位は面倒なので、db_ownerだけを付与したのですが、
テーブルへのアクセスは可能でしたが、ストアドは動きませんでした。

Manegement Studio からストアドを見ることは出来ますが、アプリから
ストアドを起動すると「オブジェクトにアクセスできません」と云った
趣旨のメッセージが表示されます。

ログインでpublic、DBユーザーでdb_ownerにチェックしました。
アプリからアクセスするには他にも必要でしょうか?

お礼日時：2009/11/17 09:05

No.4 回答者： jamshid6 回答日時： 2009/11/19 16:25

メッセージはSQL Serverが出しているものです。

エンドユーザのログインがuser1であるとして、
・user1が「勤怠_DB」においてdb_ownerである
・「SP_労務明細表」に対するEXECUTE権限を明示的にdenyしていない
（denyしていれば２番のコメントのところで実行した２つ目のクエリに出てきます）
・user1でSSMSにログインし、上記ストアドを手動実行すると実行できる

という理解なので、実行が拒否される理由が正直わからないです。
（こちらでは、いただいたソースでいろいろ試しても再現できませんので。。）

で、user1にsysadminを付けるとExcelファイルに出力できるわけですか？

＞どうもファイルへの出力が出来ないようです。
SQL Server自身がデータベース外のリソース（ファイル等）にアクセスするためには、確かにsysadminが必要ですが、メッセージは、そもそもこのストアドの実行権限がないと言っています。
（Excel出力のVBAを実行すると、SQL Serverが行うのは「EXEC SP_労務明細表 'xxxx','xxxx','xxxx'」だけであって、ファイル出力はアクセスが行います）

＞そのモジュールを使っている命令やコンボボックスの、ソースにストアドを指定してある部分はストアドも動作しています。
のように、まだコミュニケーション不足の部分があるんですかね。。

この回答への補足

＞どうもファイルへの出力が出来ないようです。
＞そのモジュールを使っている命令や・・・ストアドも動作しています。

これは私の勘違いでした、ログインに sysadmin を付けて実行していました。

sysadmin権限が無いとと、アプリからはやはりストアドは実行できませんでした。
SSMSは実行できたはずですが、再度ためしてみます。
ユーザーやログインを削除したりしても試してみます。

ありがとうございました。

補足日時：2009/11/19 16:45

No.3 回答者： jamshid6 回答日時： 2009/11/17 22:17

権限設定は正しくされているようです。

で、当方Access2003は持ってないので、Access2007/SQL Server 2008で試してみましたが、再現させられませんでした。
したがって、
・Access2003/SQL Server 2008固有の問題である
・何かもっと凝った方法でストアドを呼んでいる（私は単純にリストボックスのソースに指定して試しただけです）
なのかなと思います。

>Manegement Studio からストアドを見ることは出来ますが、アプリから
>ストアドを起動すると「オブジェクトにアクセスできません」と云った
>趣旨のメッセージが表示されます。

このメッセージの内容も正確に知りたいところです。
（ポイントはSQL Server, OLE Provider, Accessのどれが返しているメッセージなのか、です）

この回答への補足

すいません「実際にストアドを実行するモジュール」は必要なかったようです。

また、そのモジュールを使っている命令やコンボボックスの、
ソースにストアドを指定してある部分はストアドも動作しています。

どうもファイルへの出力が出来ないようです。

補足日時：2009/11/19 12:06

この回答へのお礼

-------エラーメッセージ-------------------------------------------------------------------

229 : EXECUTE 権限がオブジェクト 'SP_労務明細表'、データベース '勤怠_DB'、スキーマ 'dbo'で
拒否されました。

直下の命令 On Error で メッセージを表示させています。

-------エラーが出ている命令---------------------------------------------------------------

DoCmd.OutputTo acOutputStoredProcedure, "EXEC SP_労務明細表 '" & Me!開始日付_sp & "','" & _
Me!終了日付_sp & "'," & Nz(Me!所属コード_sp, "Null"), acSpreadsheetTypeExcel9, _
"C:\ACCESS\労務明細表_" & Format(Me!開始日付_sp, "yymmdd") & "-" & _
Format(Me!終了日付_sp, "yymmdd") & ".xls"

-------実際にストアドを実行するモジュール-------------------------------------------------

Public Function fncRunSProc(ByVal StrSProc As String, ByVal StrParam As String) As Boolean
On Error GoTo Err

Dim cn As ADODB.Connection

fncRunSProc = False

Set cn = CurrentProject.Connection

If StrParam = "" Then
cn.Execute "EXEC " & StrSProc
Else
cn.Execute "EXEC " & StrSProc & " " & StrParam
End If

cn.Close: Set cn = Nothing

fncRunSProc = True

Ext:
Exit Function
Err:
MsgBox "予期しないエラーが発生しました、下記を管理者へ連絡して下さい。" _
& vbNewLine & Err.Number & " : " & Err.Description, vbCritical
Resume Ext

End Function
------------------------------------------------------------------------------------------

よろしくお願いします。

お礼日時：2009/11/19 12:05

No.2 回答者： jamshid6 回答日時： 2009/11/17 11:16

正直、db_ownerを付けてストアドにアクセスできない状況というのがあまり想定できないんですが。

。
何か対象データベースの権限設定にゴミが残っていませんかね。

SELECT USER_NAME(role_principal_id) FROM sys.database_role_members
WHERE member_principal_id=USER_ID('ユーザ名')

SELECT * FROM sys.database_permissions
WHERE grantee_principal_id=USER_ID('ユーザ名')

SSMSにそのユーザでログインして、そのストアドは実行できるのですか？

この回答へのお礼

SELECT USER_NAME(role_principal_id) FROM sys.database_role_members
WHERE member_principal_id=USER_ID('ユーザ名')

[ db_owner ]と一件表示されました。

SELECT * FROM sys.database_permissions
WHERE grantee_principal_id=USER_ID('ユーザ名')

[ 0DATABASE00101CO CONNECTGGRANT ]と一行表示されました。

SSMSにそのユーザでログインして、そのストアドは実行できるのですか？

実行可能で、結果も正常に表示されます。
ACCESS(VBA)側の問題でしょうか?

よろしくお願いします。

お礼日時：2009/11/17 11:55