Solved

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

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

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Question has a verified solution.

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

Suggested Solutions

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
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…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how the fundamental information of how to create a table.

867 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

21 Experts available now in Live!

Get 1:1 Help Now