digitalmeandering.com

I was recently working with a database where there were a number of tables that *gasp* didn’t belong to dbo. Now this isn’t the end of the world, but best practices tend to lean towards making dbo the owner of new tables to avoid potential ownership permission issues.

Now I wouldn’t call myself lazy, but I enjoy coming up with little solutions to annyoing problems that will likely occur again. So instead of manually executing a stored procedure for each table to change the ownership to dbo, I came up with a little SQL script that generates the code to do just that, for every table that belongs to the specified user.

Good luck, god speed, and may the force be with you:
DECLARE
@currentowner varchar(20)
,@newowner varchar(20)

SET @currentowner = ’someotherdude’
SET @newowner = ‘dbo’

SELECT
so.[name] as ‘Object Name’
, su.[name] as ‘Owner Name’
FROM
sysobjects so
INNER JOIN
sysusers su
on su.uid = so.uid
WHERE
su.name = @currentowner
AND
so.xtype = ‘U’

SELECT
‘exec dbo.sp_changeobjectowner ”’ + su.[name] + ‘.’ + so.[name] + ”” + ‘, ‘ + ”” + @newowner + ”” as ‘Generate SQL!’
FROM
sysobjects so
INNER JOIN
sysusers su
on su.uid = so.uid
WHERE
su.name = @currentowner
AND
so.xtype = ‘U’