Solved

GRANT EXECUTE for stored procedure - where do I put it?

Posted on 2006-11-03
5
17,048 Views
Last Modified: 2012-06-27
Hello,

I am trying to execute a stored procedure from a VB6 application - get an error message telling me thta execute permission is denied.

Have read elsewhere on this site that I need to grant the user EXECUTE permission using a statement

GRANT EXECUTE ON <<stored procedure name>> TO <<username>>

Do I put this statement in the stored procedure itself or does it have to go elsewhere.

----- stored procedure --------

CREATE PROCEDURE dbo.sp_FreeIssueParts
AS
      exec master..xp_cmdshell 'DTSRun /S (local) /U sa /P mbl1175 /N dtsFreeIssue', NO_OUTPUT
GO


------ connection from application -------

        Set dbBookIn = New Connection
        dbBookIn.CursorLocation = adUseClient
        dbBookIn.Open "PROVIDER=MSDASQL;dsn=SCLAnodising;uid=anodising;pwd=bugger;database=SCLAnodising;"
        dbBookIn.Execute "sp_FreeIssueParts"



I believe the staement I need is:

GRANT EXECUTE ON [dbo].[sp_FreeIssueParts] TO anodising

- but where does it go?

Thank You
0
Comment
Question by:ReneLMadsen
  • 2
  • 2
5 Comments
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 17866618
>Do I put this statement in the stored procedure itself or does it have to go elsewhere.

you have to run that statement (once) apart from the procedure by a user that has the permissions to grant the permissions (usually the owner of the procedure)
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 17866632
note however that the permissions missing might be the one of the xp_cmdshell, which might be missing to the owner of the procedure.

if that is the case, the use SA for example will need to ensure the owner of the procedure (dbo) has indeed the permissions to run the xp_cmdshell procedure...
0
 

Author Comment

by:ReneLMadsen
ID: 17867082
>> if that is the case, the use SA for example will need to ensure the owner of the procedure (dbo) has indeed the permissions to run the xp_cmdshell procedure...


Where / How do I do this?
0
 
LVL 29

Expert Comment

by:Gautham Janardhan
ID: 17871976
GRANT permissions depend on the statement permissions being granted and the object involved in the permissions. The members of the sysadmin role can grant any permissions in any database. Object owners can grant permissions for the objects they own. Members of the db_owner or db_securityadmin roles can grant any permissions on any statement or object in their database
0
 

Author Comment

by:ReneLMadsen
ID: 17898312
The focus for this has changed slightly - I added the user 'anodising' to the Master database and set Execute permissions for anodising in the properties for xp..cmdshell

Now when I run the application I get the message:

Run-time error '-2147467529 (80004005)':
[Microsoft][ODBC SQL Server Driver][SQL Server]xp_cmdshell failed to execute because current security context is not sysadmin and proxy account is not setup correctly.

I'm sure it is quite apparent that I don't really know what I am doing - can someone explain to me as if talking to a child how to get this stored procedure to run

Many thanks for any help you can provide
0

Featured Post

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

776 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question