October 9, 2003 at 12:40 am
i want to change the owner of my few tables at a time .how can i do that for all.
thanks
October 9, 2003 at 1:00 am
quote:
i want to change the owner of my few tables at a time .how can i do that for all.
take a look at sp_changeobjectowner in BOL.
sp_changeobjectowner <your_table> , <your_new_owner>
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
October 9, 2003 at 1:22 am
But this takes one table at a time,i want
one procedure for all.
October 9, 2003 at 1:31 am
take a look at sp_msforeachtable -- this takes a command and runs it against every table...
Thomas Rushton
blog: https://thelonedba.wordpress.com
October 9, 2003 at 7:41 am
Check the dependency before change the owner.
October 10, 2003 at 12:25 am
The attached SP will do it for you for tables, stored procedures, views and UDFs.
If you want to filter what you change, then adjust the query which feeds the temporary tables etc. It will also reset the permissions on each object and re-assign 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_namenvarchar(128),
vc_ownernvarchar(128),
bt_usedbit)
-- filter table names here if required
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]' -- add other roles / userids as required
exec (@strExecLine)
-- grant access as required
select @strExecLine = 'grant select, insert, delete, update on ' + @strname + ' to [public]' -- add others as required
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_namenvarchar(128),
vc_ownernvarchar(128),
bt_usedbit)
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]' -- add others as required
exec (@strExecLine)
-- grant access as required
select @strExecLine = 'grant select on ' + @strname + ' to [public]'
exec (@strExecLine)
-- Add in alternative permissions here if required
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_namenvarchar(128),
vc_ownernvarchar(128),
bt_usedbit)
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]' -- ad others as required
exec (@strExecLine)
select @strExecLine = 'grant execute on ' + @strname + ' to [public]'
exec (@strExecLine)
if lower(left(@strName, 19)) = 'specific name'
begin
select @strExecLine = 'grant execute on ' + @strname + ' to otherrole'
exec (@strExecLine)
end
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_namenvarchar(128),
vc_ownernvarchar(128),
bt_usedbit)
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]' -- add others if required
exec (@strExecLine)
select @strExecLine = 'grant execute on ' + @strname + ' to [public]' -- add others if required
exec (@strExecLine)
update #udfs
set bt_used = 1
where in_rowid = @intRowid
end
drop table #udfs
GO
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply