Post reply

Change object owner.

  • March 18, 2011 at 5:51 am

    #234326

    Hello T-SQL experts,

    I just wanted to ask someone to confirm something for me, to make sure I have this correct.

    I need to do the following...

    Change a table owner from

    johnThomas.tablename

    to

    dbo.tablename

    and do the same with a couple views. Is the following correct to achieve this?

    Alter Authorisation on object::johnThomas.tablename to dbo;

    GO

    Would be able to save it as a stored procedure if I put the following before it..?

    create stored proc

    as sp_changeowner

    Thank you for reading and any help,

    D

  • March 18, 2011 at 6:27 am

    #1300441

    See Books On Line for ALTER SCHEMA

    Jim

  • March 18, 2011 at 6:37 am

    #1300447

    Thanks for the tip,

    So after running

    USE DatabaseName;

    GO

    ALTER SCHEMA dbo TRANSFER JohnThomas.tablename;

    GO

    I'll go from

    johnThomas.tablename to dbo.tablename?

    Thanks again,

    D.

  • March 18, 2011 at 8:24 am

    #1300532

    Yes.

    Jim

  • March 18, 2011 at 9:58 am

    #1300621

    Hi Jim,

    Whats the difference between what I have done above and...?

    EXEC sp_changeobjectowner 'TableName', 'dbo'

    Am I better off using this instead?

    Regards,

    D.

  • March 18, 2011 at 10:09 am

    #1300629

    From Book On Line

    This stored procedure only works with the objects available in Microsoft SQL Server 2000. This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. Use ALTER SCHEMA or ALTER AUTHORIZATION instead. sp_changeobjectowner changes both the schema and the owner. To preserve compatibility with earlier versions of SQL Server, this stored procedure will only change object owners when both the current owner and the new owner own schemas that have the same name as their database user names.

    Jim

  • March 21, 2011 at 4:05 am

    #1301168

    Once again Jim, thank you for your reply. I created a stored procedure and it appears in the database, however when I script to create into a new window, only one line of the code appears, and the 15 or so lines under it are missing, the same thing happens if I select 'Modify'. What could I be doing wrong?

    Regards,

    D.

  • March 21, 2011 at 10:32 am

    #1301411

    The GO statement marks the end of the batch and will end the procedure. Remove the GO from the middle of your create procedure script.

    ______________________________________________________________________________
    How I want a drink, alcoholic of course, after the heavy lectures involving quantum mechanics.

Viewing 8 posts - 1 through 7 (of 7 total)

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