No problem at all:

enjoy. Graham

/*

This Procedure takes all of my objects and converts them to dbo ownership and

grants access permissions to the admin role and the dbo user

what we want to do is:

       make dbo the owner of all our objects

       revoke all access to objects

       Grant access as required

*/

CREATE PROCEDURE dbo.DBO_Owners AS

declare @strExecLine nvarchar(4000)

declare @strName nvarchar(128)

declare @strOwner nvarchar(128)

declare @intRowid int

set nocount on

-- tables

-- create a temporary table to hold a list of all the user tables

create table #tables

       (in_rowid     int   identity(1,1),

       vc_name       nvarchar(128),

       vc_owner      nvarchar(128),

       bt_used              bit)

insert into #tables

       (vc_name, vc_owner, bt_used)

select sysobjects.[name], sysusers.[name], 0

from sysobjects inner join sysusers on sysobjects.uid = sysusers.uid

where sysobjects.xtype = 'u' and sysobjects.name <> 'dtproperties'

-- loop through this table and build / execute the change owner, and the grant

while exists (select * from #tables where bt_used = 0)

begin

       select top 1

              @intRowid = in_rowid, @strName = vc_name, @strOwner = vc_owner

       from #tables where bt_used = 0

       if @strOwner <> 'dbo'

       begin

              select @strExecLine = 'sp_changeobjectowner ''' + @strName + ''',''dbo'''

              --print @strExecLine

              exec (@strExecLine)

       end

       -- revoke access before regranting it

       select @strExecLine = 'revoke all on ' + @strName + ' from [public]'

       exec (@strExecLine)

       -- grant access as required

       select @strExecLine = 'grant select on ' + @strname + ' to [public]'

       exec (@strExecLine)

-- Add in alternative permissions here if required

       update #tables

       set bt_used = 1

       where in_rowid = @intRowid

end

drop table #tables

-- views

-- create a temporary table to hold a list of all the user views

create table #views

       (in_rowid     int   identity(1,1),

       vc_name       nvarchar(128),

       vc_owner      nvarchar(128),

       bt_used              bit)

insert into #views

       (vc_name, vc_owner, bt_used)

select sysobjects.[name], sysusers.[name], 0

from sysobjects inner join sysusers on sysobjects.uid = sysusers.uid

where sysobjects.xtype = 'v' and sysobjects.status > 0

-- loop through this table and build / execute the change owner, and the grant

while exists (select * from #views where bt_used = 0)

begin

       select top 1

              @intRowid = in_rowid, @strName = vc_name, @strOwner = vc_owner

       from #views where bt_used = 0

       if @strOwner <> 'dbo'

       begin

              select @strExecLine = 'sp_changeobjectowner ''' + @strName + ''',''dbo'''

              exec (@strExecLine)

       end

       -- revoke access before regranting it

       select @strExecLine = 'revoke all on ' + @strName + ' from [public] '

       exec (@strExecLine)

       -- grant access as required

       select @strExecLine = 'grant select on ' + @strname + ' to [public]'

       exec (@strExecLine)

       update #views

       set bt_used = 1

       where in_rowid = @intRowid

end

drop table #views

-- stored procedures

-- create a temporary table to hold a list of all the user procedures

create table #procs

       (in_rowid     int   identity(1,1),

       vc_name       nvarchar(128),

       vc_owner      nvarchar(128),

       bt_used              bit)

insert into #procs

       (vc_name, vc_owner, bt_used)

select sysobjects.[name], sysusers.[name], 0

from sysobjects inner join sysusers on sysobjects.uid = sysusers.uid

where sysobjects.xtype = 'p' and sysobjects.[name] not like 'dt%'                        --and sysobjects.[name] not like 'dbo%'

-- loop through this table and build / execute the change owner, and the grant

while exists (select * from #procs where bt_used = 0)

begin

       select top 1

              @intRowid = in_rowid, @strName = vc_name, @strOwner = vc_owner

       from #procs where bt_used = 0

       if @strOwner <> 'dbo'

       begin

              select @strExecLine = 'sp_changeobjectowner ''' + @strName + ''',''dbo'''

       --     print @strExecLine

              exec (@strExecLine)

       end

       -- revoke access before regranting it

       select @strExecLine = 'revoke all on ' + @strName + ' from [public] '

       exec (@strExecLine)

       select @strExecLine = 'grant execute on ' + @strname + ' to [public]'

       exec (@strExecLine)

       update #procs

       set bt_used = 1

       where in_rowid = @intRowid

end

drop table #procs

-- User Defined functions

-- create a temporary table to hold a list of all the user defined functions

create table #udfs

       (in_rowid     int   identity(1,1),

       vc_name       nvarchar(128),

       vc_owner      nvarchar(128),

       bt_used              bit)

insert into #udfs

       (vc_name, vc_owner, bt_used)

select sysobjects.[name], sysusers.[name], 0

from sysobjects inner join sysusers on sysobjects.uid = sysusers.uid

where sysobjects.xtype = 'FN' and sysobjects.[name] not like 'dt%'                       --and sysobjects.[name] not like 'dbo%'

-- loop through this table and build / execute the change owner, and the grant

while exists (select * from #udfs where bt_used = 0)

begin

       select top 1

              @intRowid = in_rowid, @strName = vc_name, @strOwner = vc_owner

       from #udfs where bt_used = 0

       if @strOwner <> 'dbo'

       begin

              select @strExecLine = 'sp_changeobjectowner ''' + @strName + ''',''dbo'''

       --     print @strExecLine

              exec (@strExecLine)

       end

       -- revoke access before regranting it

       select @strExecLine = 'revoke all on ' + @strName + ' from [public] '

       exec (@strExecLine)

       select @strExecLine = 'grant execute on ' + @strname + ' to [public]'

       exec (@strExecLine)

       update #udfs

       set bt_used = 1

       where in_rowid = @intRowid

end

drop table #udfs