Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

How to grant execute permisson on sproc to access users.

Posted on 2007-03-22
3
Medium Priority
?
487 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 2000 total points
ID: 18774694
Try this:

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

Featured Post

Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
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, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

670 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