locked
Changing the owner of objects in a DB RRS feed

  • Question

  • User-2059580246 posted

    When using tools that generate DB code and objects, and when the account used for the connection is not the one that should be the owner of the objects, you find yourself changing the generated scripts manually and hence the tool is less productive. Being lazier than this lazy programmer, I came accross a neat piece of SQL to run after the tools has generated the objects, and best of all it can be run from VS, Query Analyzer or SQL Manager Studio.

    For example, when I use the Visio Enterprise Architect ORM tool to generate a model and then my databases, Visio connects to SQL using myUser account, so tables are generated with myUser as the owner, however my application will need to use anotherUser account to comply with my customer's policies. It is also the case with CodeSmith and MyGeneration templates which generate Stored Procedures based on existing tables.

     
    SELECT 'EXEC(''sp_changeobjectowner @objname = ''''' +
       ltrim(u.name) + '.' + ltrim(s.name) + ''''''
       + ', @newowner = anotherUser )'
    FROM  sysobjects s,
           sysusers u
    WHERE s.uid = u.uid
    AND   u.name = 'myUser'
    AND   xtype in ('V', 'P', 'U')
    AND   u.name not like 'INFORMATION%'
    order by s.name
     
    The query finds views, stored procedures and user tables owned by myUser and convert ownership to anotherUser. The result looks like this:
     
    EXEC('sp_changeobjectowner @objname = ''NukedRaptor.ATC_CajeroGet'', @newowner = anotherUser')
    EXEC('sp_changeobjectowner @objname = ''NukedRaptor.ROCK_Directory_BandsAdd'', @newowner = anotherUser')
    EXEC('sp_changeobjectowner @objname = ''NukedRaptor.ROCK_Directory_BandsDelete'', @newowner = anotherUser')
    EXEC('sp_changeobjectowner @objname = ''NukedRaptor.ROCK_Directory_BandsGet'', @newowner = anotherUser')
    EXEC('sp_changeobjectowner @objname = ''NukedRaptor.ROCK_Directory_BandsList'', @newowner = anotherUser')
    EXEC('sp_changeobjectowner @objname = ''NukedRaptor.ROCK_Directory_BandsUpdate'', @newowner = anotherUser')
    
     
     Then simply pasting the lines I want, the objects' ownership will be altered.
    Saturday, December 9, 2006 10:32 AM

All replies

  • User-2041805088 posted
    Here's the script that I use to do the same thing.
    Saturday, December 9, 2006 5:42 PM