Solved

How to grant execute permisson on sproc to access users.

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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
find SQL job run average duration 24 54
t-sql, insert data in table normally, but read from view 3 30
SQL Select - identify record discrepancies 1 30
SQL Throw Error 7 33
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
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
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how the fundamental information of how to create a table.

756 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