|
|
I have a form that works fine for updates and deletes, but when I try to
insert a new record as a non-sysadmin, I get this error:
Invalid input parameter values. Check the status values for detail.
In Profiler, the last thing before the error is the sp_prepare statement for
the insert.
I am familiar with the issue with identity columns (this table is using one)
and views, however in this case the form is bound to a stored procedure, and
the stored procedure (and resync) both tie directly to the table, not to a
view.
The other interesting thing is that the insert works fine when I grant the
user sysadmin server role. Using Profiler I captured the insert that follows
the sp_prepare. I then removed the user from the sysadmin role, and ran the
insert from SSMS as that user and it worked fine.
The user normally has access to the tables through datareader/datawriter
roles, and can insert data in other forms in that same ADP.
Can someone help?
TIA,
Joel
SQL 2005 (9.00.3073.00) and Access 2003 SP3.
|
|
Is there nobody out there who can offer any insight on this matter?
"JoelB" wrote:
[Quoted Text] > I have a form that works fine for updates and deletes, but when I try to
> insert a new record as a non-sysadmin, I get this error:
>
> Invalid input parameter values. Check the status values for detail.
>
> In Profiler, the last thing before the error is the sp_prepare statement for
> the insert.
>
> I am familiar with the issue with identity columns (this table is using one)
> and views, however in this case the form is bound to a stored procedure, and
> the stored procedure (and resync) both tie directly to the table, not to a
> view.
>
> The other interesting thing is that the insert works fine when I grant the
> user sysadmin server role. Using Profiler I captured the insert that follows
> the sp_prepare. I then removed the user from the sysadmin role, and ran the
> insert from SSMS as that user and it worked fine.
>
> The user normally has access to the tables through datareader/datawriter
> roles, and can insert data in other forms in that same ADP.
>
> Can someone help?
>
> TIA,
> Joel
>
> SQL 2005 (9.00.3073.00) and Access 2003 SP3.
>
|
|
Well, this looks like some permission problem or a problem with the default
schema or a problem with the fact that Access 2003 doesn't really support
the advanced schema that you can have under SQL-Server 2005. Using advanced
schemas won't give you any problem when executing the query under SSMS but
it might well be another story when executing from ADO or under ADP. If you
have many tables with the same name but under different schemas; this could
be even worse.
What does this gives if you try to execute this query not from SSMS but from
ADP using an ADO connection such as CurrentProject.Connection?
--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)
"JoelB" <JoelB[ at ]discussions.microsoft.com> wrote in message
news:EB33D75A-01D9-4125-BE04-8B569800BD28[ at ]microsoft.com...
[Quoted Text] > Is there nobody out there who can offer any insight on this matter?
>
> "JoelB" wrote:
>
>> I have a form that works fine for updates and deletes, but when I try to
>> insert a new record as a non-sysadmin, I get this error:
>>
>> Invalid input parameter values. Check the status values for
>> detail.
>>
>> In Profiler, the last thing before the error is the sp_prepare statement
>> for
>> the insert.
>>
>> I am familiar with the issue with identity columns (this table is using
>> one)
>> and views, however in this case the form is bound to a stored procedure,
>> and
>> the stored procedure (and resync) both tie directly to the table, not to
>> a
>> view.
>>
>> The other interesting thing is that the insert works fine when I grant
>> the
>> user sysadmin server role. Using Profiler I captured the insert that
>> follows
>> the sp_prepare. I then removed the user from the sysadmin role, and ran
>> the
>> insert from SSMS as that user and it worked fine.
>>
>> The user normally has access to the tables through datareader/datawriter
>> roles, and can insert data in other forms in that same ADP.
>>
>> Can someone help?
>>
>> TIA,
>> Joel
>>
>> SQL 2005 (9.00.3073.00) and Access 2003 SP3.
>>
|
|
A second possibility would be a different "Default Schema" between the
default account used when the user is not part of the Sysadmin group and the
one used when it is. In SQL-2000, this default account is usually dbo. and
it has always be suggested that people using ADP keep using this default for
every account and that the Record Source Qualifier of every form be set to
dbo.
If you didn't follow the same rule with your project on SQL-Server 2005,
this could possibly be the source of your problem.
--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)
"Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)>
wrote in message news:ua$rP%239MJHA.5692[ at ]TK2MSFTNGP04.phx.gbl...
[Quoted Text] > Well, this looks like some permission problem or a problem with the
> default schema or a problem with the fact that Access 2003 doesn't really
> support the advanced schema that you can have under SQL-Server 2005.
> Using advanced schemas won't give you any problem when executing the query
> under SSMS but it might well be another story when executing from ADO or
> under ADP. If you have many tables with the same name but under different
> schemas; this could be even worse.
>
> What does this gives if you try to execute this query not from SSMS but
> from ADP using an ADO connection such as CurrentProject.Connection?
>
> --
> Sylvain Lafontaine, ing.
> MVP - Technologies Virtual-PC
> E-mail: sylvain aei ca (fill the blanks, no spam please)
>
>
> "JoelB" <JoelB[ at ]discussions.microsoft.com> wrote in message
> news:EB33D75A-01D9-4125-BE04-8B569800BD28[ at ]microsoft.com...
>> Is there nobody out there who can offer any insight on this matter?
>>
>> "JoelB" wrote:
>>
>>> I have a form that works fine for updates and deletes, but when I try to
>>> insert a new record as a non-sysadmin, I get this error:
>>>
>>> Invalid input parameter values. Check the status values for
>>> detail.
>>>
>>> In Profiler, the last thing before the error is the sp_prepare statement
>>> for
>>> the insert.
>>>
>>> I am familiar with the issue with identity columns (this table is using
>>> one)
>>> and views, however in this case the form is bound to a stored procedure,
>>> and
>>> the stored procedure (and resync) both tie directly to the table, not to
>>> a
>>> view.
>>>
>>> The other interesting thing is that the insert works fine when I grant
>>> the
>>> user sysadmin server role. Using Profiler I captured the insert that
>>> follows
>>> the sp_prepare. I then removed the user from the sysadmin role, and ran
>>> the
>>> insert from SSMS as that user and it worked fine.
>>>
>>> The user normally has access to the tables through datareader/datawriter
>>> roles, and can insert data in other forms in that same ADP.
>>>
>>> Can someone help?
>>>
>>> TIA,
>>> Joel
>>>
>>> SQL 2005 (9.00.3073.00) and Access 2003 SP3.
>>>
>
>
|
|
For the record, I found the solution. I had to grant 'view definition'
rights to my user (I did it through the database role I had created for my
domain group login). Apparently the db_datareader and db_datawriter are not
enough for Access in this case. After executing this script, the problem
went away:
GRANT VIEW DEFINITION ON [MySchema].[MyTable] TO [MyDatabaseRole] AS [dbo]
Sylvain---thanks for your input. Sorry I didn't reply sooner, but I didn't
get notification.
Regards,
Joel
"JoelB" wrote:
[Quoted Text] > Is there nobody out there who can offer any insight on this matter?
>
> "JoelB" wrote:
>
> > I have a form that works fine for updates and deletes, but when I try to
> > insert a new record as a non-sysadmin, I get this error:
> >
> > Invalid input parameter values. Check the status values for detail.
> >
> > In Profiler, the last thing before the error is the sp_prepare statement for
> > the insert.
> >
> > I am familiar with the issue with identity columns (this table is using one)
> > and views, however in this case the form is bound to a stored procedure, and
> > the stored procedure (and resync) both tie directly to the table, not to a
> > view.
> >
> > The other interesting thing is that the insert works fine when I grant the
> > user sysadmin server role. Using Profiler I captured the insert that follows
> > the sp_prepare. I then removed the user from the sysadmin role, and ran the
> > insert from SSMS as that user and it worked fine.
> >
> > The user normally has access to the tables through datareader/datawriter
> > roles, and can insert data in other forms in that same ADP.
> >
> > Can someone help?
> >
> > TIA,
> > Joel
> >
> > SQL 2005 (9.00.3073.00) and Access 2003 SP3.
> >
|
|
JoelB,
Thanks! I was having this same problem with an Access 2000 ADP trying to
work with SQL 2005. I added the View Definition permission to my role, and it
works now.
Mark Siltala
"JoelB" wrote:
[Quoted Text] > For the record, I found the solution. I had to grant 'view definition'
> rights to my user (I did it through the database role I had created for my
> domain group login). Apparently the db_datareader and db_datawriter are not
> enough for Access in this case. After executing this script, the problem
> went away:
>
> GRANT VIEW DEFINITION ON [MySchema].[MyTable] TO [MyDatabaseRole] AS [dbo]
>
> Sylvain---thanks for your input. Sorry I didn't reply sooner, but I didn't
> get notification.
>
> Regards,
> Joel
>
> "JoelB" wrote:
>
> > Is there nobody out there who can offer any insight on this matter?
> >
> > "JoelB" wrote:
> >
> > > I have a form that works fine for updates and deletes, but when I try to
> > > insert a new record as a non-sysadmin, I get this error:
> > >
> > > Invalid input parameter values. Check the status values for detail.
> > >
> > > In Profiler, the last thing before the error is the sp_prepare statement for
> > > the insert.
> > >
> > > I am familiar with the issue with identity columns (this table is using one)
> > > and views, however in this case the form is bound to a stored procedure, and
> > > the stored procedure (and resync) both tie directly to the table, not to a
> > > view.
> > >
> > > The other interesting thing is that the insert works fine when I grant the
> > > user sysadmin server role. Using Profiler I captured the insert that follows
> > > the sp_prepare. I then removed the user from the sysadmin role, and ran the
> > > insert from SSMS as that user and it worked fine.
> > >
> > > The user normally has access to the tables through datareader/datawriter
> > > roles, and can insert data in other forms in that same ADP.
> > >
> > > Can someone help?
> > >
> > > TIA,
> > > Joel
> > >
> > > SQL 2005 (9.00.3073.00) and Access 2003 SP3.
> > >
|
|
Excellent! I *hate* burning time on little issues like that, so it's great
to know it helped you!
Joel
"marksil" wrote:
[Quoted Text] > JoelB,
>
> Thanks! I was having this same problem with an Access 2000 ADP trying to
> work with SQL 2005. I added the View Definition permission to my role, and it
> works now.
>
> Mark Siltala
>
> "JoelB" wrote:
>
> > For the record, I found the solution. I had to grant 'view definition'
> > rights to my user (I did it through the database role I had created for my
> > domain group login). Apparently the db_datareader and db_datawriter are not
> > enough for Access in this case. After executing this script, the problem
> > went away:
> >
> > GRANT VIEW DEFINITION ON [MySchema].[MyTable] TO [MyDatabaseRole] AS [dbo]
> >
> > Sylvain---thanks for your input. Sorry I didn't reply sooner, but I didn't
> > get notification.
> >
> > Regards,
> > Joel
> >
> > "JoelB" wrote:
> >
> > > Is there nobody out there who can offer any insight on this matter?
> > >
> > > "JoelB" wrote:
> > >
> > > > I have a form that works fine for updates and deletes, but when I try to
> > > > insert a new record as a non-sysadmin, I get this error:
> > > >
> > > > Invalid input parameter values. Check the status values for detail.
> > > >
> > > > In Profiler, the last thing before the error is the sp_prepare statement for
> > > > the insert.
> > > >
> > > > I am familiar with the issue with identity columns (this table is using one)
> > > > and views, however in this case the form is bound to a stored procedure, and
> > > > the stored procedure (and resync) both tie directly to the table, not to a
> > > > view.
> > > >
> > > > The other interesting thing is that the insert works fine when I grant the
> > > > user sysadmin server role. Using Profiler I captured the insert that follows
> > > > the sp_prepare. I then removed the user from the sysadmin role, and ran the
> > > > insert from SSMS as that user and it worked fine.
> > > >
> > > > The user normally has access to the tables through datareader/datawriter
> > > > roles, and can insert data in other forms in that same ADP.
> > > >
> > > > Can someone help?
> > > >
> > > > TIA,
> > > > Joel
> > > >
> > > > SQL 2005 (9.00.3073.00) and Access 2003 SP3.
> > > >
|
|
"JoelB" <JoelB [ at ]discussions.microsoft.com> wrote in message
news:BA068E5E-6067-451F-9EAF-43713E257BBF[ at ]microsoft.com...
[Quoted Text] > For the record, I found the solution. I had to grant 'view definition'
> rights to my user (I did it through the database role I had created for my
> domain group login). Apparently the db_datareader and db_datawriter are
> not
> enough for Access in this case. After executing this script, the problem
> went away:
>
> GRANT VIEW DEFINITION ON [MySchema].[MyTable] TO [MyDatabaseRole] AS
> [dbo]
>
> Sylvain---thanks for your input. Sorry I didn't reply sooner, but I
> didn't
> get notification.
>
> Regards,
> Joel
>
> "JoelB" wrote:
>
>> Is there nobody out there who can offer any insight on this matter?
>>
>> "JoelB" wrote:
>>
>> > I have a form that works fine for updates and deletes, but when I try
>> > to
>> > insert a new record as a non-sysadmin, I get this error:
>> >
>> > Invalid input parameter values. Check the status values for
>> > detail.
>> >
>> > In Profiler, the last thing before the error is the sp_prepare
>> > statement for
>> > the insert.
>> >
>> > I am familiar with the issue with identity columns (this table is using
>> > one)
>> > and views, however in this case the form is bound to a stored
>> > procedure, and
>> > the stored procedure (and resync) both tie directly to the table, not
>> > to a
>> > view.
>> >
>> > The other interesting thing is that the insert works fine when I grant
>> > the
>> > user sysadmin server role. Using Profiler I captured the insert that
>> > follows
>> > the sp_prepare. I then removed the user from the sysadmin role, and
>> > ran the
>> > insert from SSMS as that user and it worked fine.
>> >
>> > The user normally has access to the tables through
>> > datareader/datawriter
>> > roles, and can insert data in other forms in that same ADP.
>> >
>> > Can someone help?
>> >
>> > TIA,
>> > Joel
>> >
>> > SQL 2005 (9.00.3073.00) and Access 2003 SP3.
>> >
|
|
|