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
Solved

How to grant execute permisson on sproc to access users.

Posted on 2007-03-22
3
462 Views
Last Modified: 2008-02-01
Users are getting the triggering an ODBC error that says Execute Permission Denied when a pass thru query is run that calls a sproc in SQL Server.

How do I grant them permission to run the sproc.

//* ms access code ////////////////////

Private Sub cmdQueryConnect_Click()

Dim myDB As Database
Dim qdfPassThrough As QueryDef
Dim vUSA, vCAN, vMEX, vCountry, vPartNum, vPartDesc, vStockClass As String
Dim vCustName, vSupName, vBeginDate, vEndDate, vUserName, vSQL As String

Set myDB = DBEngine.Workspaces(0).Databases(0)


/* code for populating  variables from form goes here */
       
 vSQL = "EXEC dbo.pc_QueryConnectData " & vCountry & ", " & vPartNum & ", " & vPartDesc & ", " & vCustName & ", " & vSupName & ", " & vStockClass & ", " & vBegDate & ", " & vEndDate

             
 
DoCmd.Close acQuery, "queryConnect"
DoCmd.DeleteObject acQuery, "queryConnect"

Set qdfPassThrough = myDB.CreateQueryDef("queryConnect")
qdfPassThrough.CONNECT = "ODBC; Driver={SQL Server};Server=DDDRENM112;Database=ConnectTest;Trusted_Connection=yes;"
qdfPassThrough.SQL = vSQL
qdfPassThrough.ReturnsRecords = True

DoCmd.OpenQuery "queryConnect"   <---- ERROR occurs here
DoCmd.SetWarnings False

End Sub

/* SPROC CODE */

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

/* DROP PROCEDURE [dbo].[PC_QueryConnectData]  */


CREATE PROCEDURE [dbo].[PC_QueryConnectData]

(@CustCountry varchar(20),
 @ItemCode varchar(20),
 @ItemDesc varchar(30),
 @CustName varchar(80),
 @POSupName varchar(80),
 @COPCLSCD varchar(4),
 @BegDate datetime,
 @EndDate datetime
 )

AS

SET NOCOUNT ON
( NOTE : the select statement below runs fine on its own )
SELECT T_ConnectFlatFile.CUS_COUNTRY, T_ConnectFlatFile.CUS_CURRENCY,
 T_ConnectFlatFile.COPCLSCD, T_ConnectFlatFile.ITMID,
T_ConnectFlatFile.ITMDESC, T_ConnectFlatFile.ITEM_CODE,
T_ConnectFlatFile.ITEM_DESC, T_ConnectFlatFile.REC_DATE_TIME_PST,
T_ConnectFlatFile.REC_QUANTITY, T_ConnectFlatFile.UNIT_COST,
T_ConnectFlatFile.TOTAL_COST, T_ConnectFlatFile.CUS_NAME, T_ConnectFlatFile.CUS_CITY,
 T_ConnectFlatFile.DEALER_LOC, T_ConnectFlatFile.REC_SUP_NAME
FROM dbo.T_ConnectFlatFile
WHERE (@CustCountry is Null or CUS_COUNTRY = @CustCountry)
AND (@ItemCode is Null or ITEM_CODE LIKE @ItemCode)
AND (@ItemDesc is Null or ITEM_DESC LIKE @ItemDesc)
AND (@CustName is Null or CUS_NAME LIKE @CustName)
AND (@POSupName is Null or REC_SUP_NAME LIKE @POSupName)
AND (@COPCLSCD is Null or COPCLSCD LIKE @COPCLSCD)
AND REC_DATE_TIME_PST BETWEEN IsNull(@BegDate, REC_DATE_TIME_PST) AND IsNull(@EndDate,REC_DATE_TIME_PST)





GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
0
Comment
Question by:marcon33
  • 2
3 Comments
 
LVL 16

Expert Comment

by:rboyd56
ID: 18773193
Since the connection is being made via a trusted connection. The user who is running the application has to be granted execute permission on the stored procedure

You have go to the database in Query Analyzer and execute this command:

Grant Execute on  PC_QueryConnectData to user or group

You have to specify the user or group.

It would be easiest to create a group and add the users to that group. Then grant execute on the procedure to the group. Less maintenance in the long run.
0
 

Author Comment

by:marcon33
ID: 18774667
Thanks for the response, I have a group to assign permission to but I keep getting a syntax error.
I tried two variations below neither of which worked.
Grant Execute on  PC_QueryConnectData to 'SG-PPDREN-UD-Test-Connect-Reader'
Line 1: Incorrect syntax near 'SG-PPDREN-UD-Test-Connect-Reader'.

Grant Execute on  'PC_QueryConnectData' TO 'SG-PPDREN-UD-Test-Connect-Reader'
Line 1: Incorrect syntax near 'PC_QueryConnectData'.

What next?


0
 
LVL 16

Accepted Solution

by:
rboyd56 earned 500 total points
ID: 18774694
Try this:

Grant Execute on  PC_QueryConnectData to [SG-PPDREN-UD-Test-Connect-Reader]
0

Featured Post

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
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.

861 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