User to run a stored procedure

MS SQL 2005/2008

Is there a simple way to allow a user to run a predefined stored procedure when they need to without allowing any other interaction with the database.

TIA
LVL 13
Atdhe NuhiuAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott PletcherSenior DBACommented:
Create a login and user for them.

Grant that login connect (only).

Grant the user EXEC authority on that one stored proc (only).
0
Scott PletcherSenior DBACommented:
Of course you will need to have created the stored proc not with the default of EXECUTE AS CALLER.  Instead, using EXECUTE AS 'other_user_name' would be easiest, to give the proc sufficient authority without giving it to the calling user.
0
Atdhe NuhiuAuthor Commented:
I was thinking more in terms of a front end rather than using Query analyser/SSMS as the users are not technical.

Just a select and click sort of solution.

I could write one myself I guess but wanted to avoid reinventing the wheel.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

sachitjainCommented:
What kind of interface do you want to give to your user to run that stored procedure?

If through SQL Server Management Studio then create a login for your user in desired SQL Server instance, create user for that login in desired database and give only execute permission (no other permissions on any other object) to created user over that stored procedure.

If you want to create some sort of app through ASP.Net or something as interface to user for executing stored procedure, that is also possible.
0
Anthony PerkinsCommented:
I was thinking more in terms of a front end rather than using Query analyser/SSMS as the users are not technical.
You could always write a batch file with SQLCmd.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Atdhe NuhiuAuthor Commented:
Thanks for your suggestions.

I have decided to do a dotnet console app and if I had decide that to begin with I'd have it implemented by now :-)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.