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:
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.
Contributor
6294 Points
5754 Posts
ASPInsiders
Re: Changing the owner of objects in a DB
Dec 09, 2006 05:42 PM|StrongTypes|LINK