Solved

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

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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

726 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