Solved

How to grant execute permisson on sproc to access users.

Posted on 2007-03-22
3
450 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

In this article—a derivative of my DaytaBase.org blog post (http://daytabase.org/2011/06/18/what-week-is-it/)—I will explore a few different perspectives on which week today's date falls within using Microsoft SQL Server. First, to frame this stu…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

707 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

12 Experts available now in Live!

Get 1:1 Help Now