Solved

How to grant execute permisson on sproc to access users.

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

Percona Live Europe 2017 | Sep 25 - 27, 2017

The Percona Live Open Source Database Conference Europe 2017 is the premier event for the diverse and active European open source database community, as well as businesses that develop and use open source database software.

Question has a verified solution.

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

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…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
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…
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.

623 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