Changing Table Ownership in SQL Server

Recently I had the unfortunate event of a live server being setup and used by only 1 user which wasn’t dbo. Either the programmer didn’t know any better or… well… he was the village idiot… anyway, so… on with the code. Here is a simple T-SQL statement that will get and loop through all Stored Procedures, Tables and Views and update them to use dbo (or any other user you’d like):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

DECLARE @tbl sysname
DECLARE tblcur INSENSITIVE CURSOR FOR
	SELECT name FROM sysobjects
	WHERE xtype IN ('U','V','P') AND uid = user_id('olddbuser')
OPEN tblcur
WHILE 1 = 1
	BEGIN
		FETCH tblcur INTO @tbl
			IF @@fetch_status <0
				BREAK
 
			SET @tbl = 'olddbuser.' + @tbl 
			EXEC sp_changeobjectowner @tbl, 'dbo'
	END
DEALLOCATE tblcur

Date Defaults – What every programmer should know

A few years back when I started programming my first task was to code an intranet system able to track meetings and certain tasks employees did throughout the day based on a calendar. This was before I knew what power SQL Server 2000 (and now 2005/2008) had and how easy it was working within those systems.

MS Access (which changed a lot since 1999) had one flaw. It always kept the date formats in mm/dd/yyyy (America is the “world” hahahahaha) so this made my job increasingly difficult trying to handle the dates on a South African based time (dd/mm/yyyy)

I struggled hard to understand the shameless act to which MS Access butchered my date inputs but I couldn’t help but think that this CAN’T happen to all programmers!!

After a lot of heart-ache and a lot of sleepless nights I finally found the silver bullet to the werewolf that was MS Access (and other servers worldwide running on different timezone settings)

dd-MMM-yyyy!!! Translated into: 01-Jan-1999 for example, you can insert ANY date with 100% surety that your sleep WILL guaranteed. This universal date format will work in ANY (yes you read it! ANY) database known to man and even though your raw data may look like 09/02/1999 in the database, it will always insert the correct date in the correct format in the database irrespective of what timezone the server is set on!

Classic ASP exampes of reading the date out is:

1

Day(date()) & "-" & Left(MonthName(Month(Date())),3) & "-" & Year(Date())

SQL Server example would use DateName instead of MonthName (which can be abbreviated without the Left() function I had to use in ASP) and of course, date() = getdate() in SQL Server

I would always recommend reading it out of the database (whenever possible) with stored procedures in the correct format to begin with. But it’s up to you to decide how to use this gem of knowledge I just thrown at you. And if you’re reading this thinking “But I already knew this!” then think again. Unfortunately, in 2009, there are still quite a few programmers unable to grasp simple concepts like timezones and date handling.

Remember kids. Making mistakes when you’re still new in the game is one thing. Brandishing a CV (Resume) of 3 years+ experience and still making these mistakes is just plain wrong… you chose a field where you have to evolve and look for answers constantly, don’t stagnate… I’ll haunt your dreams if you do