marcon33
asked on
How to grant execute permisson on sproc to access users.
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).Dat abases(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("query Connect")
qdfPassThrough.CONNECT = "ODBC; Driver={SQL Server};Server=DDDRENM112; Database=C onnectTest ;Trusted_C onnection= yes;"
qdfPassThrough.SQL = vSQL
qdfPassThrough.ReturnsReco rds = 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_COUN TRY, T_ConnectFlatFile.CUS_CURR ENCY,
T_ConnectFlatFile.COPCLSCD , T_ConnectFlatFile.ITMID,
T_ConnectFlatFile.ITMDESC, T_ConnectFlatFile.ITEM_COD E,
T_ConnectFlatFile.ITEM_DES C, T_ConnectFlatFile.REC_DATE _TIME_PST,
T_ConnectFlatFile.REC_QUAN TITY, T_ConnectFlatFile.UNIT_COS T,
T_ConnectFlatFile.TOTAL_CO ST, T_ConnectFlatFile.CUS_NAME , T_ConnectFlatFile.CUS_CITY ,
T_ConnectFlatFile.DEALER_L OC, 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_T IME_PST)
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
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).Dat
/* 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("query
qdfPassThrough.CONNECT = "ODBC; Driver={SQL Server};Server=DDDRENM112;
qdfPassThrough.SQL = vSQL
qdfPassThrough.ReturnsReco
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_COUN
T_ConnectFlatFile.COPCLSCD
T_ConnectFlatFile.ITMDESC,
T_ConnectFlatFile.ITEM_DES
T_ConnectFlatFile.REC_QUAN
T_ConnectFlatFile.TOTAL_CO
T_ConnectFlatFile.DEALER_L
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_T
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
ASKER
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?
I tried two variations below neither of which worked.
Grant Execute on PC_QueryConnectData to 'SG-PPDREN-UD-Test-Connect
Line 1: Incorrect syntax near 'SG-PPDREN-UD-Test-Connect
Grant Execute on 'PC_QueryConnectData' TO 'SG-PPDREN-UD-Test-Connect
Line 1: Incorrect syntax near 'PC_QueryConnectData'.
What next?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.