Stored Procedure Can not be opened in ADP form

[Farzam]

Dear All,

I have a SQL SERVER back end and Access project 2003 front end,
everything is working smootly but now i have a form which has a button on it, when i push the button it executes or opens the Stored procedure and performs actions in the background. though this works best with SA login
but when i login with different user account in ACCESS it can not run that SP.
i tried user permissions groups permissions...
but i was faild .
please help
iam using

Docmd.openprocedure.......
regards,

 

[jrbarnett]

You need to grant execute permission on that stored procedure to the account that is being used to run the application, or preferably.

Have a role in your database with the permissions needed for your application (including execute permission for this procedure)
Have an account set up on the server that is a member of this role.

Then, you login with this account and have the permissions needed to run your application.

John

 

[Crowley16]

you can also manually specify the account and password to use against the connection string, of course this is a security flaw since you have to hard code in a username and password.

--------------------
Procrastinate Now!

 

[Farzam]

jrbarnett > I just did everything you said.
I set permission for ROLE and added the member to that role and also i give individual permission to user for SP.

It is working with SA (DBO) very fine, but not with other accounts even having full permission i am really confused.

is there anyother way to or CODE to user instead of
DoCmd.OpenStoredProcedure......

here is my code have a look

-----------------------------------------------------------
Dim msg, Style, Title, Response
msg = "The pipeline contract will permanently be converted to an actual contract" & vbNewLine & "Do you want to accept this conversion to occur?" ' Define message.
Style = vbYesNo + vbCritical + vbDefaultButton2 ' Define buttons.
Title = "convertion" ' Define title.

Response = MsgBox(msg, Style, Title)
If Response = vbYes Then: GoTo nextmain

'ElseIf Response = vbNo Then ' User chose No.
Me.Undo
Exit Sub




nextmain:
DoCmd.SetWarnings False


If Me.CRIS_No.Value <= 10001 Then

MsgBox ("Please enter a Real CRIS number and then convert")
Me.CRIS_No.SetFocus
Else: GoTo nextcondition
End If
Exit Sub
nextcondition:

Dim strvisitid As Integer

On Error GoTo Err_CRIS_NO_AfterUpdate

'Call DoCmd.RunCommand(acCmdSaveRecord)

Me.SetFocus
If Me.Dirty = True Then

DoCmd.RunCommand acCmdSaveRecord
End If
'DoCmd.RunCommand acCmdSaveRecord
DoCmd.OpenStoredProcedure "Contract_Temp_converter", acViewNormal, acReadOnly
DoCmd.OpenStoredProcedure "Contract_Temp_Deleter", acViewNormal, acReadOnly
DoCmd.OpenStoredProcedure "Contract_temp_forecast_deleter", acViewNormal, acReadOnly
DoCmd.OpenForm "Contract_temp_convert_timer", acNormal
DoCmd.Close acForm, "contract_temp_singleed"


DoCmd.SetWarnings True
Exit_CRIS_NO_AfterUpdate:

Exit Sub

Err_CRIS_NO_AfterUpdate:
If (Err.Number <> "30014") Then
MsgBox Err.Description
Else


Me.SetFocus
If Me.Dirty = True Then

DoCmd.RunCommand acCmdSaveRecord
End If
'DoCmd.RunCommand acCmdSaveRecord
DoCmd.OpenStoredProcedure "Contract_Temp_converter", acViewNormal, acReadOnly
DoCmd.OpenStoredProcedure "Contract_Temp_Deleter", acViewNormal, acReadOnly
DoCmd.OpenStoredProcedure "Contract_temp_forecast_deleter", acViewNormal, acReadOnly
DoCmd.OpenForm "Contract_temp_convert_timer", acNormal
DoCmd.Close acForm, "contract_temp_singleed"

DoCmd.SetWarnings True

End If


Resume Exit_CRIS_NO_AfterUpdate



DoCmd.SetWarnings True
----------------------------------------------------------
regards,

 

[Farzam]

Problem was and still is with ROLEs , when i apply System Administrator Role to members they work fine ..as SA.
but applying such role makes everyone as administrator...
but when i create my own role and give specific permissions including to stored procedures ... they dont work...
can you find me plz what is the problem why user rolse are not accepted.

 

[jrbarnett]

This is more relevant to Forum958 or Forum183, but here goes:

If you connect to SQL Server using that account and password with Query Analyser (or SQL Server Management Studio):

1. Does it switch to the database that contains your application data as its default?

2. can you run

EXEC storedprocedurename

(putting the name of your stored procedure in there) from QA or SSMS and get your expected result as output?

If both of these are expected, then the problem is with the Access project itself; if these don't work, then it is with the SQL Server permissions or connection information.

John

 

[jrbarnett]

Just read your post in more detail and have noticed:

also i give individual permission to user for SP

Don't do this - give permission for the role to execute the stored procedure, then make your account a member of the role.

John

 

[Farzam]

yes i can access the default database when i login, to QA.
also i can exec procedures.

but the question is when i put the user into SERVER ROLE (System Administrator) it works.
but it never works with other ROLEs.

if something would be worng with Access project it should not work with SA either?

can you think on this more plz.

regards,