G
Guest
Hi all,
I hope you can help me with this one as our db admin person has left. I have
a SQL backend with a ADP frontend. Within the frontend i have set up a combo
box that contains a list of all the access reports you can run, and another
combo that contains criteria for those reports. Behind each report is a
stored procedure that gets the parameters from the form. eg
CREATE PROCEDURE dbo.rptGenus(@genus varchar(255))
AS SELECT dbo.Plant.ID, dbo.Plant.Genus, dbo.Plant.Species,
dbo.Plant.CountryofOrigin, dbo.PlantAccession.Location,
dbo.PlantAccession.AccessionNo, dbo.Plant.SubSpecies, dbo.Plant.Family,
dbo.Plant.Synonym, dbo.Plant.CommonName, dbo.Plant.PlantForm,
dbo.Plant.Hybrid, dbo.Plant.Cultivar, dbo.Plant.TradeName, dbo.Plant.Variaty,
dbo.PlantAccession.PlantStatus
FROM dbo.Plant
INNER JOIN dbo.PlantAccession ON dbo.Plant.ID = dbo.PlantAccession.ID
WHERE (dbo.PlantAccession.PlantStatus IS NULL) AND (dbo.Plant.Genus =
@genus) OR
(dbo.PlantAccession.PlantStatus IS NULL) AND (@genus = '<All>')
ORDER BY dbo.Plant.Genus, dbo.Plant.Species, dbo.PlantAccession.AccessionNo
GO
Behind the report that uses this query on the reports properties - parameter
is @Genus=[Forms]![Main]![frmReporting]![cmbCriteria]
cmbCriteria is a comb box on a form that the user selects either the plant
genus that they want the report on or the <All> option for all plant genus's.
This all works fine when i run the reports as I have full rights to the
entire database. but as soon as i apply a Active Directory group to the sql
backend these users can run the reports but it doesn't return any data. This
is the same
thing that happens when they try to run the stored proc behind the report.
These Users have execute rights on the stored procedure and read/write
rights on all tables.
I am guessing its becuase they dont have the correct access to the stored
procedure. Because it takes in parameters i dont think those users have
access to change the stored procedure when they select a different parameter.
how can i give them rights to change this stored procedure?
Hope this makes sense, let ne know if there more info is needed.
Thanks in advance
Tanya
I hope you can help me with this one as our db admin person has left. I have
a SQL backend with a ADP frontend. Within the frontend i have set up a combo
box that contains a list of all the access reports you can run, and another
combo that contains criteria for those reports. Behind each report is a
stored procedure that gets the parameters from the form. eg
CREATE PROCEDURE dbo.rptGenus(@genus varchar(255))
AS SELECT dbo.Plant.ID, dbo.Plant.Genus, dbo.Plant.Species,
dbo.Plant.CountryofOrigin, dbo.PlantAccession.Location,
dbo.PlantAccession.AccessionNo, dbo.Plant.SubSpecies, dbo.Plant.Family,
dbo.Plant.Synonym, dbo.Plant.CommonName, dbo.Plant.PlantForm,
dbo.Plant.Hybrid, dbo.Plant.Cultivar, dbo.Plant.TradeName, dbo.Plant.Variaty,
dbo.PlantAccession.PlantStatus
FROM dbo.Plant
INNER JOIN dbo.PlantAccession ON dbo.Plant.ID = dbo.PlantAccession.ID
WHERE (dbo.PlantAccession.PlantStatus IS NULL) AND (dbo.Plant.Genus =
@genus) OR
(dbo.PlantAccession.PlantStatus IS NULL) AND (@genus = '<All>')
ORDER BY dbo.Plant.Genus, dbo.Plant.Species, dbo.PlantAccession.AccessionNo
GO
Behind the report that uses this query on the reports properties - parameter
is @Genus=[Forms]![Main]![frmReporting]![cmbCriteria]
cmbCriteria is a comb box on a form that the user selects either the plant
genus that they want the report on or the <All> option for all plant genus's.
This all works fine when i run the reports as I have full rights to the
entire database. but as soon as i apply a Active Directory group to the sql
backend these users can run the reports but it doesn't return any data. This
is the same
thing that happens when they try to run the stored proc behind the report.
These Users have execute rights on the stored procedure and read/write
rights on all tables.
I am guessing its becuase they dont have the correct access to the stored
procedure. Because it takes in parameters i dont think those users have
access to change the stored procedure when they select a different parameter.
how can i give them rights to change this stored procedure?
Hope this makes sense, let ne know if there more info is needed.
Thanks in advance
Tanya