Professional Web Applications Themes

query on changing object owner - Microsoft SQL / MS SQL Server

I need to change object ownership for all objects (sp's, tables, views, etc) from SGLuser to dbo for a sql 7 db. Can anyone give me an idea of the script involved (either using sp_changeobjectowner or other) ? Any help appreciated. TIA,...

  1. #1

    Default query on changing object owner

    I need to change object ownership for all objects (sp's, tables, views, etc)
    from SGLuser to dbo for a sql 7 db. Can anyone give me an idea of the script
    involved (either using sp_changeobjectowner or other) ? Any help
    appreciated.

    TIA,




    LP Guest

  2. #2

    Default Re: query on changing object owner

    Create a cursor containing all the objects (tables, views, ... etc.).
    For each object execute sp_changeobjectowner.

    --
    Dean Savovic
    [url]www.teched.hr[/url]


    "LP" <londonporayahoo.co.uk> wrote in message
    news:OGdcqNvRDHA.212TK2MSFTNGP10.phx.gbl...
    > I need to change object ownership for all objects (sp's, tables, views,
    etc)
    > from SGLuser to dbo for a sql 7 db. Can anyone give me an idea of the
    script
    > involved (either using sp_changeobjectowner or other) ? Any help
    > appreciated.
    >
    > TIA,
    >
    >
    >
    >

    deeeoo Guest

  3. #3

    Default Re: query on changing object owner

    For tables, you could use the undoented sp_msForEachTable, e.g. try this
    repro script:


    USE MASTER
    GO

    EXEC sp_addlogin 'blat', 'blat', 'master'
    CREATE DATABASE blat
    GO

    USE blat
    GO

    EXEC sp_adduser 'blat', 'blat'

    CREATE TABLE blat.bar(foo INT)
    CREATE TABLE blat.foo(bar INT)
    GO

    -- note TABLE_SCHEMA column here, that's the owner
    SELECT * FROM INFORMATION_SCHEMA.TABLES

    -- run changeobjectowner for all tables
    EXEC sp_msForEachTable 'EXEC sp_changeobjectowner ''?'', ''dbo'''

    -- note TABLE_SCHEMA column here, that's the owner
    SELECT * FROM INFORMATION_SCHEMA.TABLES

    USE MASTER
    GO

    DROP DATABASE blat
    EXEC sp_droplogin 'blat'
    GO


    For the other objects, you will need to run cursors, I think, from the
    INFORMATION_SCHEMA views...


    "LP" <londonporayahoo.co.uk> wrote in message
    news:OGdcqNvRDHA.212TK2MSFTNGP10.phx.gbl...
    > I need to change object ownership for all objects (sp's, tables, views,
    etc)
    > from SGLuser to dbo for a sql 7 db. Can anyone give me an idea of the
    script
    > involved (either using sp_changeobjectowner or other) ? Any help
    > appreciated.
    >
    > TIA,
    >
    >
    >
    >

    Aaron Bertrand - MVP Guest

Similar Threads

  1. CF requiring owner name prefix to tablename during query
    By AndyDC in forum Coldfusion Database Access
    Replies: 2
    Last Post: May 4th, 08:53 AM
  2. changing 3D object color
    By maryneedhelp in forum Macromedia Director 3D
    Replies: 3
    Last Post: September 30th, 02:46 PM
  3. Changing the texture of a linked object
    By Martin in forum Macromedia Director 3D
    Replies: 0
    Last Post: December 6th, 09:12 PM
  4. Changing Vector Object Dimensions
    By Chester Harold in forum Macromedia Fireworks
    Replies: 0
    Last Post: September 16th, 02:47 PM
  5. Changing The owner of the Computer
    By Yolanda Giles in forum Windows Setup, Administration & Security
    Replies: 3
    Last Post: July 6th, 04:32 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139