Error on insert: Invalid input parameter values...

J

JoelB

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.
 
S

Sylvain Lafontaine

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?
 
S

Sylvain Lafontaine

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.
 
J

JoelB

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
 
M

marksil

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 said:
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 said:
Is there nobody out there who can offer any insight on this matter?
Click to expand...
Click to expand...
 
J

JoelB

Excellent! I *hate* burning time on little issues like that, so it's great
to know it helped you!

Joel

marksil said:
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 said:
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 said:
Is there nobody out there who can offer any insight on this matter?

:

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.
Click to expand...
Click to expand...
Click to expand...
 
S

susan roads

JoelB said:
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 said:
Is there nobody out there who can offer any insight on this matter?
Click to expand...
Click to expand...
 
G

Guy Kerr

JoelB thanks a BUNCH for finding this solution and posting it. I have been
having this issue for over a year now and new it was permissions related but
couldn't find the solution. Thanks to you, I no longer have to make all my
data entry users local administrators on our SQL SERVER!!! OUCH!

Thanks again - SINCERELY!

Guy Kerr

JoelB said:
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 said:
Is there nobody out there who can offer any insight on this matter?
Click to expand...
Click to expand...
 

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

Similar Threads

Call stored procedure in SSMS from Access 2016 doesn't work. 0
Error on insert: "Invalid input parameter values" 0
Object is invalid. Extended properties are not permitted on '<*>.< 2
Parameter Value error in a Subform 0
Access Invalid bracketing of parameter name 1
Form recordsource parameter sp change 2
Invalid Column Name Sql-Error :Executing a stored procedure 1
office XP and AC2000 ADP on a SQL 7 server. 1

Top