Technology Smokey Mountain

SQL practices

The curse and blessings of dynamic SQL


Excellent texts in sQL


This is a small collection of texts about some features in SQL Server that people frequently ask about in the newsgroups about MS SQL Server. Rather than being a FAQ with many questions with short answers, these are in-depth articles on some of my favourite topics

Refactorings - Improving your code

Alpha list of Refactorings

An SQL Blogger in ASP.NET

Source of useful tips on sql and about the new Yukon.
Carter's Dad

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 @ 11/10/2003 7:58 PM by Darren Neimke