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