Solved

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

Posted on 2006-11-03
5
17,038 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

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

Suggested Solutions

Performance is the key factor for any successful data integration project, knowing the type of transformation that you’re using is the first step on optimizing the SSIS flow performance, by utilizing the correct transformation or the design alternat…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

758 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now