SQL ADP Reports not working due to Authentication

T

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
 
T

Tanya

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

Thanks again
 
T

Tanya

Hi All,

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

Tanya

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

Thanks again

Tanya said:
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
 
S

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)


Tanya said:
Hi All,

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

Tanya

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

Thanks again

Tanya said:
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
 
T

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

Sylvain Lafontaine said:
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)


Tanya said:
Hi All,

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

Tanya

Tanya said:
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top