Table record editing permissions

From: ChrisB (anonymous_at_discussions.microsoft.com)
Date: 12/02/03 

Date: Tue, 2 Dec 2003 12:17:03 -0800

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

>-----Original Message-----
>I'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.
>
>>-----Original Message-----
>>
>>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
>>
>>>-----Original Message-----
>>>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
>>>
>>>>-----Original Message-----
>>>>
>>>>This is correct, you cannot edit linked SQLServer
>tables
>>>>from MSAccess. You must edit in SQLServer using either
>>>>Enterprise Manager or Query Analyzer.
>>>>
>>>>You explained the problem is setting permissions, I
>>>assume
>>>>you were doing this in Enterprise Manager. Keep in
mind
>>>>permissions are set at the table level, not column or
>>row
>>>>level. If the EM gui isn't working properly try using
>>>>Query Analyzer, select your user database, and execute
>>>the
>>>>following:
>>>>
>>>>GRANT SELECT, INSERT, UPDATE, DELETE ON
TABLE_NAME_HERE
>>>TO
>>>>USER_NAME_HERE
>>>>
>>>>Perform this for each table.
>>>>
>>>>Hope this helps,
>>>>ChrisB
>>>>MCDBA MCSE
>>>>
>>>>>-----Original Message-----
>>>>>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
>>>>>
>>>>>.
>>>>>
>>>>.
>>>>
>>>.
>>>
>>.
>>
>.
>