i'm looking for a script to change the ownership of some tables in SQL
database from a certain user to dbo
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!Hi.
You can utilize something as this:
sp_changeobjectowner [ @.objname = ] 'object' , [ @.newowner = ] 'owner'
This it is a stored procedure of SQL Server and only is able used by the use
rs in the roles SysAdmin and db_owner.
Hermilson Tinoco.
****************************************
*************
i'm looking for a script to change the ownership of some tables in SQL
database from a certain user to dbo|||Thanks for your reply.
Via other sources I've got a mail with the following script which
creates a stored procedure that you can execute to change the ownership
of several tables in one run:
CREATE PROC dbo.up_FixObjOwners
AS
SET NOCOUNT ON
DECLARE @.dynsql varchar(1000)
SET @.dynsql = ''
DECLARE @.Obj_Owner sysname
SET @.Obj_Owner = ''
DECLARE @.Obj_Type VARCHAR(30)
SET @.Obj_Type = ''
DECLARE @.Obj_Name sysname
SET @.Obj_Name = ''
DECLARE @.ObjCounter INT
SET @.ObjCounter = 0
DECLARE @.DBO CHAR(3)
SET @.DBO = 'DBO'
-- temp table to hold all objects not owned
-- by DBO
create table #ChangeOwners(
id int identity(1,1),
Obj_Owner sysname,
Obj_Name sysname,
Obj_Type varchar(30))
-- populate it
INSERT #ChangeOwners (Obj_Owner, Obj_Name, Obj_Type)
select
su.name,
so.name,
case
when type = 'u' then 'table'
when type = 'p' then 'sproc'
when type = 'v' then 'view'
end as obj_type
from sysusers su
join sysobjects so
on su.uid = so.uid
where su.name not in ('information_schema', 'dbo')
and so.type in ('p', 'u', 'v')
-- select * from #ChangeOwners
SET @.ObjCounter = @.@.rowcount -- holds the count of rows inserted into
#ChangeOwners
WHILE @.Objcounter > 0
BEGIN
-- construct string for object ownership change
SELECT @.Obj_Name = Obj_Owner + '.' + Obj_Name FROM #ChangeOwners WHERE
id = @.ObjCounter
SELECT @.Obj_Type = Obj_Type FROM #ChangeOwners WHERE id = @.ObjCounter
SET @.dynsql = 'sp_ChangeObjectOwner ''' + @.Obj_Name + ''', ' + @.DBO
--select @.dynsql
print 'changing ownership on ' + @.Obj_Type + ': ' + @.Obj_Name
EXEC(@.dynsql)
SET @.ObjCounter = @.ObjCounter - 1
END
-- ok all done, collect garbage
drop table #ChangeOwners
I hope u can use some other time too.
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!|||Note that changing the object owner (or renaming the proc) will not change
the underlying source text of the procedure. This can cause problems with
subsequent DDL scripting if the original owner was explicitly specified on
the CREATE statement.
Hope this helps.
Dan Guzman
SQL Server MVP
"stevesilent" <stevesilent@.devdex.com> wrote in message
news:OV1wf8J8DHA.2412@.TK2MSFTNGP09.phx.gbl...
> Thanks for your reply.
> Via other sources I've got a mail with the following script which
> creates a stored procedure that you can execute to change the ownership
> of several tables in one run:
> CREATE PROC dbo.up_FixObjOwners
> AS
> SET NOCOUNT ON
> DECLARE @.dynsql varchar(1000)
> SET @.dynsql = ''
> DECLARE @.Obj_Owner sysname
> SET @.Obj_Owner = ''
> DECLARE @.Obj_Type VARCHAR(30)
> SET @.Obj_Type = ''
> DECLARE @.Obj_Name sysname
> SET @.Obj_Name = ''
> DECLARE @.ObjCounter INT
> SET @.ObjCounter = 0
> DECLARE @.DBO CHAR(3)
> SET @.DBO = 'DBO'
> -- temp table to hold all objects not owned
> -- by DBO
> create table #ChangeOwners(
> id int identity(1,1),
> Obj_Owner sysname,
> Obj_Name sysname,
> Obj_Type varchar(30))
> -- populate it
> INSERT #ChangeOwners (Obj_Owner, Obj_Name, Obj_Type)
> select
> su.name,
> so.name,
> case
> when type = 'u' then 'table'
> when type = 'p' then 'sproc'
> when type = 'v' then 'view'
> end as obj_type
> from sysusers su
> join sysobjects so
> on su.uid = so.uid
> where su.name not in ('information_schema', 'dbo')
> and so.type in ('p', 'u', 'v')
> -- select * from #ChangeOwners
> SET @.ObjCounter = @.@.rowcount -- holds the count of rows inserted into
> #ChangeOwners
> WHILE @.Objcounter > 0
> BEGIN
> -- construct string for object ownership change
> SELECT @.Obj_Name = Obj_Owner + '.' + Obj_Name FROM #ChangeOwners WHERE
> id = @.ObjCounter
> SELECT @.Obj_Type = Obj_Type FROM #ChangeOwners WHERE id = @.ObjCounter
> SET @.dynsql = 'sp_ChangeObjectOwner ''' + @.Obj_Name + ''', ' + @.DBO
> --select @.dynsql
> print 'changing ownership on ' + @.Obj_Type + ': ' + @.Obj_Name
> EXEC(@.dynsql)
> SET @.ObjCounter = @.ObjCounter - 1
> END
> -- ok all done, collect garbage
> drop table #ChangeOwners
>
> I hope u can use some other time too.
>
> *** Sent via Developersdex http://www.examnotes.net ***
> Don't just participate in USENET...get rewarded for it!
My fingers typed one =