Results 1 to 11 of 11
  1. #1
    2 Star Lounger
    Join Date
    Oct 2002
    Location
    Lakewood, New Jersey, USA
    Posts
    187
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Permissions Problem (Access 2002/SQL Server 2000)

    I have an Access 2002 front-end containing tables linked to a SQL Server 2000 backend. My ODBC connection uses Windows NT Authentication. I granted my user name (TD/tjuni) FULL permissions to the database in question. I am a member of the db_owner group, and have explicitly granted myself all permissions on all tables. I've used EM to confirm that I am being recognized as TD/tjuni on SQL Server when I open my Access front-end. The problem: I still can't delete records from a particular table. Also, I can only add records to this table if I delete the link to the table and recreate the link (using the same ODBC File DSN - mind you). Can anyone provide any insight as to what I'm missing here?

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Permissions Problem (Access 2002/SQL Server 2000)

    Can you delete records if you go to Query Analyzer and login as yourself, not administrator? I presume the issue may be that you don't have a unique index on the table in question, so Access refuses to let you delete or edit records. Also, you appear to still be using a File DSN - we find that using a System DSN works more reliably.
    Wendell

  3. #3
    2 Star Lounger
    Join Date
    Oct 2002
    Location
    Lakewood, New Jersey, USA
    Posts
    187
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Permissions Problem (Access 2002/SQL Server 2000)

    Wendell,
    I just tried using Query Analyzer to delete a record (I logged in using windows authentication) and was unable to. I got the 229 Error (Delete Permission denied on object...). I checked the indexes on the table and discovered that there was indeed no unique index. I created a primary key based on two columns: ID and DBOrigin. However, I still can't delete a row using Query Analyzer. (BTW - I tried using a system dsn - didn't work either. Why is it more reliable? Also, if I use a system dsn, do I have to set it up on every user's machine?). Thanks for your prompt and on-target replies!

  4. #4
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Permissions Problem (Access 2002/SQL Server 2000)

    If you can't delete a record or records in Query Analyzer, then that sounds like you don't have delete permissions on the object. Have you given your NT domain login the permissions necessary to delete (or joined it to a role that has the necessary permissions)?
    Wendell

  5. #5
    2 Star Lounger
    Join Date
    Oct 2002
    Location
    Lakewood, New Jersey, USA
    Posts
    187
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Permissions Problem (Access 2002/SQL Server 2000)

    I gave my id full permissions to the database. I also made myself a db_owner and a member of the system Administrators group.

  6. #6
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Permissions Problem (Access 2002/SQL Server 2000)

    Then something is definitely amok - are you certain you are loggin in as who you think you are? Also, if you login as 'sa' can you delete a record in table in question? It is possible to set a DENIED permission in SQL Server that overrides other settings, so you might check that.
    Wendell

  7. #7
    2 Star Lounger
    Join Date
    Oct 2002
    Location
    Lakewood, New Jersey, USA
    Posts
    187
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Permissions Problem (Access 2002/SQL Server 2000)

    I'm positive that I'm logging in as my domain name/username because I can view my current activity using EM. If I log in to query analyzer using my SQL Server login (i.e. using SQL Server authentication), I can delete records, no problem. How would I find this DENIED permission that you mentioned?

  8. #8
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Permissions Problem (Access 2002/SQL Server 2000)

    In Enterprise Manager, right click on the table in question, and choose properties. That brings uup a dialog box that has a Permissions button - click that and then look at the user in question and see if there is a red X - that's a denied. It could also be inherited from a role assigned to that user, so you if you don't find it at the user level, check the roles for a denied on that object. Hope this helps - sorry I can't be more responsive, but I'm traveling and with clients most of the day.
    Wendell

  9. #9
    2 Star Lounger
    Join Date
    Oct 2002
    Location
    Lakewood, New Jersey, USA
    Posts
    187
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Permissions Problem (Access 2002/SQL Server 2000)

    Wendell - Your responsiveness is very much appreciated. I know that you're busy and really appreciate the time you're able to devote to answering my (and all of our) questions. I checked in EM just to ensure that permissions were not denied to my login name or that of any role of which I am a member. No denied permissions. I am supposedly a sys admin and a db_owner on the database in question. I have full permissions specified on the table. I even tried to use GRANT PERMISSIONS in query analyzer to grant myself UPDATE, INSERT, and DELETE permissions on the particular table and supposedly the commands were executed successfully. I can't imagine why I'm not allowed to delete using my domain name/login. If you have any other ideas, I'd be thrilled to hear them.

  10. #10
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Permissions Problem (Access 2002/SQL Server 2000)

    Sorry - I spent most of yesterday in airports and airplanes. Do you get some sort of error message when you try to delete a record? At this point I'm as mystified as you - but it undoubtedly has something to do with the permissions or lack thereof. If it were being blocked by DRI or a trigger, you would be getting an error when you deleted using query analyzer. If we can't come up with a better answer, you might consider creating a new table, copying all the records into it, and then deleting the old table, and renaming the new to the old.
    Wendell

  11. #11
    2 Star Lounger
    Join Date
    Oct 2002
    Location
    Lakewood, New Jersey, USA
    Posts
    187
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Permissions Problem (Access 2002/SQL Server 2000)

    After writing my last post, I tried something else. I gave the Public role Delete permissions and found that I was then able to delete records from the table while logged in as Domain nameusername. Is it possible that, because my username is a member of the Public role as well as more powerful roles, the Public role is determining my permissions? I tried removing myself from the Public role but I am not allowed to. Any ideas?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •