I hate going back and changing the owner on objects to dbo. While this stored proc is useful, I don’t like having to find all the different user names and running it for each one. So I wrote this proc to generate change owner scripts for all objects in a database not owned by dbo.
Installation
Run the sql script to create the stored proc in whatever database you choose. Prefix it with sp_ and put it in the master database if you expect to use it across several databases on the same SQL Server instance and you don’t want to duplicate it in each database (and you have permissions).
Usage
Type this in SQL Server Query Analyzer and run it:
ChangeAllObjectOwnersToDBO
In the results pane will be the text to change the owner of all non-dbo-owned objects to dbo. Copy all of the results text, paste in Query Analyzer main window, and run. A warning message will be displayed if there is already an object of the same name owned by dbo. You should resolve these problems on an individual basis.
You should see cautionary statements like this:
Caution: Changing any part of an object name could break scripts and stored procedures.
Don’t worry, that’s standard stuff. Hopefully you haven’t hard-coded a specific owner! If so, I don’t think this stored proc will help.
if exists (select * from sysobjects where id = object_id(N‘[dbo].[ChangeAllObjectOwnersToDBO]‘) and OBJECTPROPERTY(id, N‘IsProcedure’) = 1)
drop procedure [dbo].[ChangeAllObjectOwnersToDBO]
GO
SET QUOTED_IDENTIFIER OFF SET ANSI_NULLS ON
GO
CREATE proc ChangeAllObjectOwnersToDBO
as
set nocount on
declare @uid int
declare @objName varchar(50)
declare @userName varchar(50)
declare @currObjName varchar(50)
declare @outStr varchar(256)
set @uid = user_id(‘dbo’)
declare chObjOwnerCur cursor static
for
select user_name(uid) as ‘username’, [name] as ‘name’ from sysobjects where uid <> @uid
open chObjOwnerCur
if @@cursor_rows = 0
begin
print ‘All objects are already owned by dbo!’
close chObjOwnerCur
deallocate chObjOwnerCur
return 1
end
fetch next from chObjOwnerCur into @userName, @objName
while @@fetch_status = 0
begin
set @currObjName = ‘dbo.’ + @objName
if (object_id(@currObjName) > 0)
print ‘WARNING *** ‘ + @currObjName + ‘ already exists ***’
set @outStr = ‘sp_changeobjectowner ”’ + @userName + ‘.’ + @objName + ”’, ”dbo”’
print @outStr
print ‘go’
fetch next from chObjOwnerCur into @userName, @objName
end
close chObjOwnerCur
deallocate chObjOwnerCur
set nocount off
return 0
GO
SET QUOTED_IDENTIFIER OFF SET ANSI_NULLS ON
GO
Geoff – and sweet script! It uses SQL like it’s supposed to be used (set-based processing), versus creating a cursor and iterating like a programming language. I didn’t ever bother to look at the script, just tweaked what was there to suit my requirements.
Geoff – yep, anytime you hit sysobjects Microsoft absolves themselves of caring whether an upgrade breaks your stuff. You are supposed to use the INFORMATION_SCHEMA view (which is a SQL standard), but they don’t provide access to everything under the covers! Damned if you do, damned if you don’t.
Hey Darrel,
I was needing this exact thing yesterday. My boss at work had a script that did it, but i forgot to take it home with me, and i needed to run it at home
So I used yours, and was happy, but i thought you might be interested in his, since it’s much much much shorter. It’s hard coded for dbo, but i imagine it’s easy to make that a param coming into an SP instead.
Anyway, here tis.
SELECT ‘EXEC(”sp_changeobjectowner @objname = ””’
+ ltrim(u.name) + ‘.’ + ltrim(s.name)
+ ”””
+ ‘, @newowner = dbo”)’
FROM
sysobjects s,
sysusers u
WHERE
s.uid = u.uid
AND u.name <> ‘dbo’
AND xtype in (‘V’, ‘P’, ‘U’)
AND u.name not like ‘INFORMATION%’
order by
s.name
He did tell me it’s heavily reliant on knowing on how SQL7 and 200 work, and has no garantees it’ll work in yukon
Try changing the variable declarations to this:
declare @objName varchar(100)
declare @userName varchar(100)
declare @currObjName varchar(100)
declare @outStr varchar(1000)
Hey man, I’m having a little trouble. When I type "ChangeAllObjectOwnersToDBO" I copy the results and paste into new window. Everything is fine there, but when I run the commands, I get this:
———————————–
sp_changeobjectowner ‘user939811.spCCTypeFetch’, ‘dbo’
go
Error: No objects owned by user939811.spCCTypeF
———————————–
I looks like SQL Server chops off object name: instead of "user939811.spCCTypeFetch", I only get "user939811.spCCTypeF". Of course, the script fails. I know it’s not your fault, but do you know why SQL Server is behaving this way?
Thanks a lot!
Tomas
Anytime Brendan! I’ll have some more good SQL stuff here soon.
Yo D. Never told you how much I love this, but I’ve used it a bunch of times… You da man.
Thanks Darrell, worked like a charm!
Great ! saved a lot of our time
But what about this: http://www.winnetmag.com/SQLServer/Article/ArticleID/21890/21890.html
jan@van-veldhuizen.nl
Jeff – yes, actually I linked to it in my blog post and talked about what changes I made to improve it.
Ever read MS Article #275312???
Enjoy!
Saved my butt, and lots of hours of my time. Thanks!!!
Awesome. Thank you.
Thanks for this procedure.
I have installed this on all my sql server instances.
Glad you like it!
This is awesome. A great timesaver !!!
Thanks,
Sunil
Great thanx a lot!
This is a great tool
You run the stored proc to get a text output. Then copy the output to a NEW query analyzer window, and then run it. That should work.
it seems that is doesnt rename the stored prodedure
any idea why?
Great post! Thanks.
This is Amazing man ,I already implemented in my production server
This is great! Thanks for sharing.
Yeah, I had too!
Man, this is great! I can really put this to use right away on my current project. Wait, you already took care of it.