SQL ADP Reports not working due to Authentication

[Tanya]

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

 

[Tanya]

btw this doesn't seem to be effecting the forms at all but then none of the
forms use parameters.

Thanks again

 

[Tanya]

Hi All,

I have worked out what has gone wrong. Thanks to anyone that has looked into
it for me.

Tanya

btw this doesn't seem to be effecting the forms at all but then none of the
forms use parameters.

Thanks again

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

 

[Sylvain Lafontaine]

Maybe you could tell us how you did to resolve your problem in order to help
other peoples.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)

Hi All,

I have worked out what has gone wrong. Thanks to anyone that has looked
into
it for me.

Tanya

btw this doesn't seem to be effecting the forms at all but then none of
the
forms use parameters.

Thanks again

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

 

[Tanya]

Hi Sylvain,

its kinda embaressing the query's were all wrong, the lineshould be
INNER JOIN dbo.PlantAccession ON dbo.Plant.ID = dbo.PlantAccession.Plant_ID

Im not sure how it got changed cause it was working when i was testing on my
account and then stopped after we applied the nt authentication.

Tanya <---slightly embarressed

Maybe you could tell us how you did to resolve your problem in order to help
other peoples.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)

Hi All,

I have worked out what has gone wrong. Thanks to anyone that has looked
into
it for me.

Tanya

btw this doesn't seem to be effecting the forms at all but then none of
the
forms use parameters.

Thanks again

:

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