Table record editing permissions

Table record editing permissions

Post by Ron L » Wed, 03 Dec 2003 22:59:01


I'm a newbie to SQL Server 2000 and I've got a problem I
can't resolve. I recently downloaded the trial version of
the server. I've learned how to create a database with its
associated data table and fields. I've also learned how to
create and connect my database to an Access project.

But here's my problem: I can't edit my data table records
via my Access project. I've already been through the
process of setting permissions to insert, update, etc, my
data table. But when I try to set permissions to select
and update columns, the list of columns disappear when
selecting the button, "list only columns with permissions
for this user". The user is Public. If I select the
button, "list all columns", I can check the boxes to
select and update the columns but still I can't edit my
records. Can someone tell me what I'm doing wrong in
trying to set permissions to allow anyone to edit my data
table records?

Thanks in advance - Ron
 
 
 

Table record editing permissions

Post by Chris » Wed, 03 Dec 2003 23:33:40


By the bye... anytime you change SQL table schema you must
refresh your table linkes in MSAccess. In other words,
delete link then re-create.

ChrisB
MCDBA MCSE
www.MyDBA2000.com

its
to

 
 
 

Table record editing permissions

Post by anonymou » Thu, 04 Dec 2003 00:17:26

I tried your suggestion but unfortunately it didn't work.
I have one table named, "Table1" and two users, 'dbo'
and 'public'. I executed your statement for each user on
Table1 and it didn't resolve the problem. Any other
suggestions?

Thanks Ron

assume
the
TO
of
records
my
permissions
data
 
 
 

Table record editing permissions

Post by Chris » Thu, 04 Dec 2003 01:19:05


What user account are you using in MSAccess to link your
tables to SQLServer? Are you using an ODBC DSN connection.
In your ODBC settings is where you specify your user. If
your table is owned by dbo you should be able to connect
using the sa account. If you use sa you do not need to
specify permissions, sa is the owner of the table. However
this is unsafe connecting with the admin acct, you should
create a new user, grant permissions to this user, then
link via Access using this new user account.

Don't forget to refresh your table links...

ChrisB
MCDBA MCSE
www.MyDBA2000.com

row
I
 
 
 

Table record editing permissions

Post by Ron L » Thu, 04 Dec 2003 05:01:56

'm using the 'dbo' account. And the Access Database User
Properies reflect full table permission for the db_owner
and public role members. The server login tab does
show 'sa' but its 'grant access' properties are greyed out.

connection.
However
work.
tables
how
select
my
 
 
 

Table record editing permissions

Post by Ronald Lan » Thu, 04 Dec 2003 05:02:34

I have 'dbo' as the database user with all table permissions granted as
shown in the Access Database User Properties.



*** Sent via Developersdex http://www.yqcomputer.com/ ***
Don't just participate in USENET...get rewarded for it!
 
 
 

Table record editing permissions

Post by Chris » Thu, 04 Dec 2003 05:17:03

The 'dbo' account means that 'sa' is the owner. No
permissions need to be granted to an object owner,
especially when the owner is 'sa' or the system
administrator. The owner can INSERT/UPDATE/DELETE at-will
without being granted permissions. When you login remotely
use sa as the user account in Access to test.

If you still have trouble with deleting or editing in
Access, refresh your table links.

Also, Access will not allow your table to be updated if
Access does not have a primary key specified. When linking
tables Access prompts you to make a column on the table
the PK. Don't forget this step. You can also go into the
MSAccess table design window and specify a PK. Try that...

Chris B
MCDBA MCSE
www.MyDBA2000.com

out.
should
on
mind
TABLE_NAME_HERE
problem
version
with
etc,
when
 
 
 

Table record editing permissions

Post by Ronald Lan » Thu, 04 Dec 2003 21:21:38

PROBLEM SOLVED! Thanks for your help. The culprit was having no primary
key in my tables. In the past, I've created Access database tables using
Visual Basic 6.0 and never had a problem not assigning primary keys. But
apparently, Access doesn't like SQL database tables with no primary
keys.

Ron



*** Sent via Developersdex http://www.yqcomputer.com/ ***
Don't just participate in USENET...get rewarded for it!