using sp_changeobjectowner for multiple objects
-
I have about 50 SPROCS in which the owner needs to be changed. Is there an easy T-SQL way to do this. Thanks for any help.
Kevin
Thanks For your continued Help. -
Are all the sp owners in the db going to be the same?
Or do you have a list of those to be changed in a table?
-
yes, I am going to change them all to dbo
Thanks For your continued Help. -
This is what I use:
It works for Tables, Stored Procedures, Views and UDFs. It's not that elegant, but when builing a new database, it is usually the first thing I add in. That way, each time we create a new object, we run it, and everything is given the correct ownership and permissions. I have trimmed out my special permissions and added in just generic ones. I assign special permissions baed on the name of each object and it works very well.
Note that sp_changeobjectowner only works on objects which you own and doesn't like to work on a dbo owned object. It used to in SQL7.0 but not in SQL2000. This is hence the check to see if the object is owned by dbo before changing the owner.
Also if you have a number of developers creating objects using windows authentication, such that there are a number of different owners, only the owner of an object can change its owner. ie John cannot change Fred's objects.
Hope this helps
/*
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 permissions as appropriate
*/
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]' -- Plus other ids if required
exec (@strExecLine)
-- Grant access as required
select @strExecLine = 'grant select on ' + @strname + ' to [public]' -- Access / ids 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_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
GO
-
This is what I use:
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
-
Thanks to both of you, I really appreciate your time and assistance. I will take the above TSQL and start exploring. I am an SQL 2000 newbie so I will take my time to process.
Thanks For your continued Help. -
There is a proc on the MSDN that I use and it creates a list of statements that you copy and paste back into QA. I have thought of modifying it so I could specify to change only views or procedures or functions, but haven't done it.
chObjOwner MSDN 3_23_03 (Microsoft Knowledge Base Article - 275312)
-
From QA, this will give you the statements that you will need to run in order to change the objects that are not owned by 'dbo' to have 'dbo' as the owner.
select 'exec sp_changeobjectowner ''[' + s.name + '].' + o.name + ''', ''dbo'''
from sysobjects o inner join sysusers s on o.uid = s.uid
where s.name <> 'dbo'
order by o.name
Just paste the results into a QA window and execute it.
Hope this helps.
Jarret
-
This cursor will change the owner to dbo of all objects in the database (sprocs, views, etc)
It works on SQL2000, it should work on SQL 7
You can modify it to change the owner to whom ever...
DECLARE @@tablename varchar(100)
DECLARE @@tablename_header varchar(200)
DECLARE tnames_cursor CURSOR FOR
select ltrim(TABLE_SCHEMA) + '.' + ltrim(TABLE_NAME)
from [databasename].information_schema.tables
where (not (TABLE_SCHEMA = 'dbo'))
order by TABLE_SCHEMA,TABLE_NAME
OPEN tnames_cursor
FETCH NEXT FROM tnames_cursor INTO @@tablename
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
SET @@tablename_header = 'sp_changeobjectowner @objname = '
+ char(39) + RTRIM(UPPER(@@tablename)) + char(39)
+ ' , @newowner = ' + char(39) + 'dbo' + char(39)
PRINT @@tablename_header
exec ( @@tablename_header )
END
FETCH NEXT FROM tnames_cursor INTO @@tablename
END
DEALLOCATE tnames_cursor
-
I would probably go the route that Jarret suggested. Mainly because I want to make sure that I'm not doing something I shouldn't. This allows me to edit/double check each statement before executing it.
Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB EngineerThis posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.
-
This is, basically, the same except that it uses the information_schema views and not the sys* tables
-
I liked Jarret's T-SQL; so, I took the liberty of improving it(imho) a little bit.
SET NOCOUNT ON
Declare @Statement as nvarchar(500)
DECLARE _cursor CURSOR FOR
select
'exec sp_changeobjectowner ''['
+ s.name
+ '].'
+ o.name
+ ''', ''dbo'''
from
sysobjects o inner join
sysusers s on o.uid = s.uid
where
s.name 'dbo'
order
by o.name
-- =============================================
-- Create temporary table to hold results...
-- =============================================
CREATE TABLE #Temp (
[STATEMENT] nvarchar(500) NOT NULL
)
OPEN _cursor
FETCH NEXT FROM _cursor
INTO @Statement
PRINT '-------- working --------'
WHILE @@FETCH_STATUS = 0
BEGIN
Insert Into #Temp Select @Statement
Insert Into #Temp Select 'GO'
-- Get the next @Statement.
FETCH NEXT FROM _cursor
INTO @Statement
END
CLOSE _cursor
DEALLOCATE _cursor
Select * from #Temp
drop table #Temp
PRINT '--------- done ----------'
Go
It adds the perennial GO statement in a separate line. I hope is not overkill,
it saves me having to pull up the regEx editor in Notepad 2(which also rocks); before pumping back into QA.
{Francisco} -
I doubt I would go as far as using a temp table and a cursor for this. You can do the same without either.
If you send your results to text or to a file instead of grids in QA, this will add the 'go' to it:
select 'exec sp_changeobjectowner ''[' + s.name + '].[' + o.name + ']'', ''dbo''
go'
from sysobjects o inner join sysusers s on o.uid = s.uid
where s.name <> 'dbo'
order by o.name
After looking at this again, I did notice that I left off the brackets around the table name in my original post, which I have included this time.
-
Hi, just sticking to what works for me.
I tried a few things: including appending char(10) and char(13) combos.
How do YOU send the results to text or to a file as you mentioned?
I tried the above instructions(>>select all>>save as...) and wasn't successful. Is there a trick?
Thanks.
{Francisco} -
It won't print correctly if you output to grids, which is why I mentioned it. If your string spans multiple lines (like the case with my code), when displaying the results in grids, it will display the whole string on one line (I think it puts 2 spaces in place of the carriage returns).
Within Query Analyzer, there are 3 ways to get your results, they are (with hotkeys to change):
Results in Grids - Ctrl+D
Results in Text - Ctrl+T
Results to File - Ctrl+Shift+F
You can also go to Tools -> Options, in the Results tab, you can change the way the results are displayed with the 'Default results target:' option.
During your session in QA, press Ctrl+T and run the code I gave. It will put the 'go' on the next line.
This will do what you need.
When you are done, you can press Ctrl+D to get the results back to grids.
Hope this helps.
Jarret
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply