ShowUsYour<Blog>

Irregular expressions regularly

Change the owner of all db objects

It was after running the sql scripts to install a new database that I noticed that the author had not prefixed the objects with "dbo." and so, after running them I noticed that they were owned by the user that I was logged-in as at the time (super_administrator!). Well, it wasn't going to be very useful to have everyone logging in using that account to run the sprocs so, I wrote the following script to enumerate the tables and sprocs and assign a new owner: 

DECLARE @currentObject nvarchar(517)
DECLARE @qualifiedObject nvarchar(517)
DECLARE @currentOwner varchar(50)
DECLARE @newOwner varchar(50)

SET @currentOwner = 'ASPNET'
SET @newOwner = 'dbo'

DECLARE alterOwnerCursor CURSOR FOR
SELECT [name] FROM dbo.sysobjects 
WHERE xtype = 'U' or xtype = 'P'
AND LEFT([name], 2) <> 'dt'
OPEN alterOwnerCursor
FETCH NEXT FROM alterOwnerCursor INTO @currentObject
WHILE @@FETCH_STATUS = 0
BEGIN
   SET @qualifiedObject = CAST(@currentOwner as varchar) + '.' + CAST(@currentObject as varchar)
   EXEC sp_changeobjectowner @qualifiedObject, @newOwner
   FETCH NEXT FROM alterOwnerCursor INTO @currentObject
END
CLOSE alterOwnerCursor
DEALLOCATE alterOwnerCursor
Posted: Nov 10 2003, 07:58 PM by digory | with 15 comment(s)
Filed under:

Comments

Jules said:

Neat. My method of doing this was far more convoluted, lol.
# November 10, 2003 6:04 AM

Darrell said:

I am generally against using cursors. Cursors take away the wonderful set-based abilities of SQL Server and force it to look at things row-by-row. Probably not a big deal in this case, but there is almost always a set-based solution. Like this one from Alceu Almeida which includes error handling and transaction capabilities(http://searchdatabase.techtarget.com/tip/1,289483,sid13_gci551691,00.html?FromTaxonomy=%2Fpr%2F286331):

SELECT * from sysobjects where uid = user_id('UseNAme')
declare @Return int
exec @Return = sp_configure 'allow updates', '1'
SELECT @Return as 'Returned Code'
GO
reconfigure WITH OVERRIDE
GO
DECLARE @Rows int, @Error int
BEGIN TRANSACTION
update sysobjects set uid = user_id('dbo') where uid = user_id('UseNAme')
SELECT @Error = @@Error, @Rows = @@RowCount
SELECT @Rows as '#Rows'
IF @Rows > 0
BEGIN
SELECT @Rows AS '#Rows'
COMMIT TRANSACTION
END
else
BEGIN
SELECT @Error AS 'Error #'
ROLLBACK TRANSACTION
END

exec sp_configure 'allow updates', '0'
reconfigure WITH OVERRIDE
go
# November 10, 2003 9:14 AM

Darren Neimke said:

Thanks for that Darrell... it's always nice to know the pure set-based way of doing it; alas my sql skills are not quite what they used to be.
# November 10, 2003 2:08 PM

TrackBack said:

Dewayne Mikkelson and his Radio WebDog, Shadow
# December 26, 2003 3:48 AM

Sakha - Techie Gyaaan » Change ownership of all objects in SQL Server 2000 said:

Pingback from  Sakha - Techie Gyaaan &raquo; Change ownership of all objects in SQL Server 2000

# February 29, 2008 8:42 AM

Scott Michie said:

Thanks for the script... solved my headache

Note to others:  If you have any long object names, add a size to the CAST to varchar like so:

SET @qualifiedObject = CAST(@currentOwner as varchar(500)) + '.' + CAST(@currentObject as varchar(500))

cast has a default size of 30 for the converted data type.

SEE: msdn.microsoft.com/.../ms187928.aspx

# February 9, 2009 5:47 PM

antarpreet said:

Hi, I urgently need a script to chage the schema in SQL 05. The above one is not compatiable!

Coudl you provide me a suitable link!

Thanks,

Antarpreet

# February 21, 2009 7:00 AM

bedava oyunlar said:

Thanks for that Darrell..

# February 28, 2009 2:22 PM

MarkRight said:

Great post you got here. I'd like to read a bit more concerning this matter.

# October 27, 2009 8:24 AM

Jane said:

Among other things, the best method to protect yourself from spy devices and irritative calls is to use <a href="www.jammer-store.com/">Phone blocker</a>. Disable cell phones around you.

# November 1, 2009 6:17 PM

Yohan said:

The best way to relax after hard day is to spend time with sweet escort women from <a href="www.baccaratgirls.com/.../a>. This escort service is greatly in demand in London.

# November 2, 2009 12:32 AM

Summer SeriousCloun said:

Indeed great post u got here. It'd be really cool to read a bit more about that matter. Thnx for giving such info.

# May 11, 2010 9:41 AM

Avril Hakkinen said:

Rather nice place you've got here. Thanks the author for it. I like such topics and everything that is connected to them. I definitely want to read more on that blog soon.

Avril Hakkinen    

<a href="milanescorts.com/">agenzie escort milano</a>

# March 20, 2011 1:07 AM

weblogs.asp.net said:

36691.. He-he-he :)

# May 12, 2011 6:08 AM

weblogs.asp.net said:

36691.. Slap-up :)

# June 26, 2011 8:16 AM
Leave a Comment

(required) 

(required) 

(optional)

(required)