Results 1 to 4 of 4
  1. #1
    3 Star Lounger
    Join Date
    Apr 2002
    Location
    Redditch, Worcestershire, England
    Posts
    233
    Thanks
    0
    Thanked 22 Times in 21 Posts

    Access to SQL Server migration (2000 SR1)

    I have been asked to assist with the migration of some Access data (my area of expertise in my organisation) to SQL Server (currently no-one's area of expertise in the organisation!!).

    I've amassed quite a bulk of info on the mechanics of upsizing etc., but one thing which I think is important to get right in a project like this is the security on the server side. In order for me to be able to manipulate/correct/amend all the upsized objects, create new procedures, relationships etc., what level of access should my security administrator be giving out (once he's trained!!)?

    Do I get dbcreator, which presumably means I could go round leaving a trail of abused databases?

    OR

    should the DBA create the target database (empty) and transfer ownership to me

    OR

    should he create the db, retain ownership, and give me membership of some other group (dbowner, ddladmin??) to enable me to manipulate the internal objects in full??

    I don't know if there are any DBAs here who get involved in the upsizing process, but presumably other developers on this forum have been through the same process and can recommend a sound procedure.

    Thanks

    Jeremy

  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 to SQL Server migration (2000 SR1)

    Sorry for the long delay - flu and the holiday - in responding. There are conflicting views on who should get what, especially if you have a separate organization in charge of maintaining servers and doing DBA work. In generaly you will want dbowner if you are making design changes, but the actual owner of the database will need to be DBO so that things like backups and maintenance plans can execute correctly. In our experience, the DBAs almost never get involved in the upsizing process, and tend to view it as suspect. In point of fact, there is some truth to that. The upsizing wizard can be flaky (it wil fail to upsize a table for mysterious reasons), but when it works it is quite useful. However, the real power comes in some of the new constructs you can have in SQL, such as views, stored procedures and triggers. Of course you also get gains in performance and reliability.

    If you have the opportunity and the inclination, I would do a simple upsizing project and play with the various options, understand the security model, have a look at SQL Server vulnerabilities from internal and external sources, and decide whether the ODBC connection route is the one you want to use. If you already have split databases, that will require the least changes in your user-interface front-ends. Hope this gives some guidance. Feel free to post with further questions or issues.
    Wendell

  3. #3
    3 Star Lounger
    Join Date
    Apr 2002
    Location
    Redditch, Worcestershire, England
    Posts
    233
    Thanks
    0
    Thanked 22 Times in 21 Posts

    Re: Access to SQL Server migration (2000 SR1)

    Thanks Wendell.

    I think I've got my head round the authority needed to do certain things, and I was aware of the distinction between dbo and dbowner.

    One thing I'm not clear on though - if I'm connecting as a user in the dbowner role (but not as the actual dbo), does the upsizing wizard allow me to specify an owner for a target table, e.g. if I want [tblData] to go to [dbo].[tblData]? I understand this is important when end users will want to query the data, as otherwise they (or their queries) would have to specify the table owner prefix.

    Jeremy

  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 to SQL Server migration (2000 SR1)

    Based on recent experience, the answer as to who the owner ends up being in the scenario you describe is not dbo. It is the SQL UserID of person who did the upsizing. However it is fairly straightforward to reassign ownership to DBO, solving the prefix problem as well as those associated with backups and maintenance. On the other hand, I wouldn't expect end users to be working with tables at the SQL level either. Where you are doing that sort of thing, I would expect them to be using the Access front-end. Another point - you will probably want to rename the tables in access to get rid of the dbo_ prefix; at least we ususally do. Good luck.
    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
  •