Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    Apr 2003
    Location
    Beaverton, Oregon, USA
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Access ADP security (MSA 2000/SQL 7.5)

    I've been working on converting some of my Access/Access databases to ADP/SQL versions. I'm having problems understanding exactly how I need to set up security on the SQL side. I've created users and logins, assigned them to their roles, and set permissions for the role. The problem is, if I don't set each user to System Administrator then none of my forms work. All the tables in the ADP file seem to get renamed dbo.*. Is this normal? Should I have created them diferently, or am I just not getting their security correct?

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

    Re: Access ADP security (MSA 2000/SQL 7.5)

    Security on ADP projects is a bit of a challenge - one of the reasons we only use them when we must have the best performance possible. Not sure why people wouldn't be able to open a form if they logged into SQL as something other than SA - unless they can't actually use the tables with their own login. Re your question on table names starting with dbo. that is normal if you upsize from Access or export from Access - dbo stands for "data base owner" and is one of the security objects in SQL Server. Are you using the Desktop Engine or the full version of SQL Server?
    Wendell

  3. #3
    New Lounger
    Join Date
    Apr 2003
    Location
    Beaverton, Oregon, USA
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access ADP security (MSA 2000/SQL 7.5)

    Performance isn't an issue in this case. Max of about 25 users at a time, and really more than 10 is unlikely.

    We're using NT logins. When I created all the tables and views I created them as my login, which is set for System Admin. I didn't upsize from Access,. and none of the objects contain the dbo prefix in my login. It's only when I log in as someone else (so far, any login that isn't a Sys Admin.) that they appear.


    Does it matter how my account is set up when creating objects?

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

    Re: Access ADP security (MSA 2000/SQL 7.5)

    I think you will find that working with ADPs in Access 2000 is somewhat frustrating - support in 2002 is significantly better, but still well behind the tools you get if you use ODBC to connect to SQL Server tables. (2002 also gets you the SQL Sever 2000 Desktop Engine which has lots of nice features and improvements over SQL 7.)

    The Data Base Owner (dbo.) prefix gets attached to any table if you aren't the dbo - which explains why you don't see them and others do. However I believe that code references and form and report references you create will still work OK with that prefix. A similar issue exists with ODBC linked tables, but there we rename the tables once they've been linked. Let me point you to a couple of references that may be useful for working with ADPs - the documentation for them is not great. Have a look at <!mskb=318816>Microsoft Knowledge Base Article 318816<!/mskb> and <!mskb=253180>Microsoft Knowledge Base Article 253180<!/mskb>.
    Wendell

Posting Permissions

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