Post reply

database owner

  • June 9, 2005 at 1:07 pm

    #67536

    My customer upsized his Access database tables. Then we linked the Access front end to the SQL server. But instead of seeing the tables named dbo_Customer and dbo_Orders we are seeing timothyl_Customer and timothyl_Orders.

    Obviously, he was not a member of the system administrators when he created the tables, so he is the owner. Now my stored procedures, etc., which refer to dbo.Customer do not work!

    How can I fix this so that I can get back to dbo.filename?

  • June 9, 2005 at 1:26 pm

    #564364

    Run this in query analyser :

    Select 'exec sp_changeObjectOwner [' + O.Name + '], [dbo]' as ExecSQL from dbo.SysObjects O where O.XType = 'U' and UID 1 order by O.Name

    Paste the results from that query back into query analyser and run it. All your tables have their ownership transfered to dbo.

  • June 10, 2005 at 4:27 am

    #564532

    If I am right, you are working in Access not SQL so the Change Owner Sp will not work.

    All you need do is rename the linked table entries in the Tables View of Access


    Sean

  • June 10, 2005 at 4:42 am

    #564534

    You can change the table owner to dbo with sp_changeobjectowner.  You will have to re-link the tables in Access after doing this.

    If you want any new tables from that user to be created as dbo. then you need to make that user the database owner using sp_changedbowner.

    Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.

    When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara

  • June 10, 2005 at 6:42 am

    #564554

    Still faster to relink that to rename manually .

  • June 10, 2005 at 7:02 am

    #564570

    Thank you very much.

  • June 10, 2005 at 7:13 am

    #564573

    HTH.

  • June 10, 2005 at 7:39 am

    #564589

    I have found it easier to make a quick VBA routine to drop all links and reestablish them as I need them. It takes no time to do and save a lot in the future. Especially if you have to a lot of db change.

    Kindest Regards,
    David

    ** Obstacles are those frightening things that appear when we take our eyes off the goal. **

  • June 10, 2005 at 7:44 am

    #564593

    Can you post the code?

  • June 10, 2005 at 1:11 pm

    #564764

    You will not have the problem in an Access Data Project, (ADP).  This may not be a good solution if you have a lot of queries because you have to convert them to sql views.

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply