Server Roles

[metevil80]

Hello all, I have a third party payment web site. The problem is that when a customer logs in to pay a bill everything is fine until the payment is processed there is no confirmation page (HTTP 500 error displays). On the elmah log it displays a SQL permissions error. I tried giving the web user several DB level permissions and still nothing, but if the user is assigned a server level permission then the confirmation page appears and everything is sunshine and rainbows. This is obviously not an acceptable solution. Is anyone aware of something that can be done on the server side without giving this user a server level admin permission?

 

[SQLBill]

You don't say what the SQL Server error is, so it's almost impossible to suggest a solution. Let us know what the error is and maybe we can suggest appropriate permissions.

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[metevil80]

Sorry about that. The Elmah is stating an error of:
System.Data.SqlClient.SqlException (0x80131904): The User does not have permission to perform this action.

The developers have looked through the stored procedure and nothing within the sp is causing this permissions issue.

 

[SQLBill]

Without knowing what the stored procedure is doing, it's difficult to say what the issue is. You also don't say what version of SQL Server you are using (SS2000, SS2005, SS2008??) and some permissions are different based on the version.

Are they creating an object? They might need db_ddladmin.

I suggest looking in the BOL at the Permissions of Fixed Database Roles (this is from SS2005 BOL: ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/a08108a3-f1fb-43ac-a264-3f2f9749db5d.htm). You might be able to match up what they are doing to permissions.

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[SQLBill]

(for SS2005/SS2008) also, are they trying to pull data from sp_help stored procedures - is so, they need VIEW ANY DEFINITION. If they are trying to pull data from the Dynamic Management Views - they need VIEW SERVER STATE.

Again...it's almost impossible to tell you what permissions are needed as we can't see what they are trying to run, access, or do.

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[yelworcm]

A quick Google(tm) search shows that that error message can come up when a user tries to access a Dynamic Management View (DMV). Can you run a trace of the application with SQL Profiler and track RPC:Starting, and SQL:BatchCompleted (may not be exact match on those names) to see what the offending statement is? If the call is buried in a stored procedure, you may need to track another event (StmtCompleted).

 

[djj55]

Something as simple as a TRUNCATE statement can cause permission errors within a stored procedure.

djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!

 

[metevil80]

Thank you for the suggestions. There is a stored procedure that the trace points to. this stored procedure adds the payment to the CSM. I am again working with the developers today on reintalling the customer web payment software on a test server and attempting to make payments while running a trace. I worked with the developers for about 10 hours the other day doing the same and there was nothing that opped out at us. I will bring these suggestions with me and see if we can come to a resolve.