Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2006-11-03
5
Medium Priority
?
17,091 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 1500 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 143

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

Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

Question has a verified solution.

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

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
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.
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

661 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