Changing the owner of objects in a DB RSS

• • Reply
  hooligannes9...

  Participant

  972 Points

  2845 Posts

  Changing the owner of objects in a DB

  Dec 09, 2006 10:32 AM|hooligannes97|LINK

  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.

  
• • Reply
  StrongTypes

  Contributor

  6294 Points

  5754 Posts

  ASPInsiders

  Re: Changing the owner of objects in a DB

  Dec 09, 2006 05:42 PM|StrongTypes|LINK

  Here's the script that I use to do the same thing.