Security Question

  • Thread starter Thread starter Bob
  • Start date Start date
B

Bob

Access2007 with SQL2005

I am logged into a test DB using an .adp.
The user is part of a role that is a member in
both db_datareader and db_datawriter.

From the .adp, I can see the tables,.. open them, ..
but I can not Insert or Update.

When I connect to the same DB as the same user in SSMS,
i can open the table and Insert and update without a problem.

What do I need to do in the Access .adp to allow that user
to Insert and Update?

thanks in advance,
...bob
 
Not sure of what I will say - because I don't use this configuration
myself - by I think that having db_datareader and db_datawriter is not
sufficient because Access don't have access to the design of the tables and
as such, don't know how to make the updates. By granting the view
definition rights to the role, you should be OK. You have to write
something in the line of:

GRANT VIEW DEFINITION ON [MySchema].[MyTable] TO [MyDatabaseRole] AS [dbo]

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Email: sylvain2009 sylvainlafontaine com (fill the blanks, no spam please)
Independent consultant and remote programming for Access and SQL-Server
(French)
 
Hello Sylvain,
Thanks for the reply and the advice.
...what the problem wound up being was
that the table in my test DB did not have a PK.
(kicked myself for missing this)
I Altered the table, indexing the PK
and re-opened the .adp as the same
user and all is well. He can now insert and
update without any problem.

Thanks again for the help Sylvain!
...bob


Sylvain Lafontaine said:
Not sure of what I will say - because I don't use this configuration
myself - by I think that having db_datareader and db_datawriter is not
sufficient because Access don't have access to the design of the tables and
as such, don't know how to make the updates. By granting the view
definition rights to the role, you should be OK. You have to write
something in the line of:

GRANT VIEW DEFINITION ON [MySchema].[MyTable] TO [MyDatabaseRole] AS [dbo]

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Email: sylvain2009 sylvainlafontaine com (fill the blanks, no spam please)
Independent consultant and remote programming for Access and SQL-Server
(French)


Bob said:
Access2007 with SQL2005

I am logged into a test DB using an .adp.
The user is part of a role that is a member in
both db_datareader and db_datawriter.

From the .adp, I can see the tables,.. open them, ..
but I can not Insert or Update.

When I connect to the same DB as the same user in SSMS,
i can open the table and Insert and update without a problem.

What do I need to do in the Access .adp to allow that user
to Insert and Update?

thanks in advance,
..bob
Click to expand...
Click to expand...
 
Back
Top