Stored procedure with input variable

[barbarao]

Hi, Access front end and SQL backend. I want to have a stored procedure with an input variable so users can be prompted for what they want thus saving me time from having to generate on the fly standard reports. I have no problem building it but when I do, I am the only one for whom it works. How do I make it available to everyone? Thanks in advance.

[Rabbit]

You or a database administrator will need to give everyone permission to execute the stored procedure on the SQL Server.

[ck9663]

Unless your front-end uses the same account to connect to the SQL Server.

Good Luck!!!


~~ CK

[barbarao]

@Rabbit
I'm the administrator but have never done this. Can you stir me in the right direction? Everyone has a separate log on to the SQL server. Thanks.

[ck9663]

Are there any other stored procedure that currently being used? If they can use it, they might probably be able to use your new one.

~~ CK

[Rabbit]

You right click the procedure, click on properties, click on permissions. Add the users and give them execute permission.

@ck, I've always had to add permissions on any newly created procedures.

[ck9663]

Yes, Good practice :)

[barbarao]

@Rabbit
Thanks. I'll try that and let you know on Monday.

[barbarao]

@Rabbit
I did what you suggested and no luck. My server is off site and the folks that run the cloud tried things and the latest I heard is that they can run the stored procedure directly off SQL Mgt Studio so they think my issue has something to do with the Access form. Do you have any ideas as that makes no sense to me. Thanks much!

[Rabbit]

You would need to tell us how you're calling the stored procedure from your form and any error messages you get.

[barbarao]

@Rabbit
I have a form (Access) with command buttons. The code for one button is

  Dim stDocName As String
 
    stDocName = "spAffiliationsPrompt"
    DoCmd.OpenStoredProcedure stDocName, acViewNormal, acReadOnly
 
Exit_Command6_Click:
    Exit Sub

When I click on the button the stored procedure prompt comes up. When anyone else where I work clicks on it, they get a message about the object cannot be found. I went to someone's machine and looked behind the scenes and was able to see the stored procedure and to run it (through "Queries") but no one but me can run it from the command button on click. Thanks in advance.

[barbarao]

I figured it out. It was a matter of having dbo in the name of the procedures. Thanks for your time and as usual, your support.

[ck9663]

I think that's just because you're running it from Access. If it's just SQL Server, "dbo" is the default schema.

Happy Coding!!!


~~ CK