The other day we were going through some old scripts and had a huge discussion about changing object ownership in SQL.  Forever the only answers were to recreate the objects or use the stored procedures: sp_changeobjectowner or sp_changedbowner.  Alternatively at times there were those of us who used some other undisclosed hack of system tables but, I’ve never done anything like that…


Since SQL 2005 Alter Authorization has been the right way to change ownership of nearly anything

This is the most common use of alter authorization but there are many more.

sp_changeobjectowner would look like this

sp_changeobjectowner 'Products.Description','AllenK'

While the updated syntax would look like this

ALTER AUTHORIZATION ON OBJECT::[Products].[Description] TO [AllenK]

What really brought this post about was because I was working with certificates recently and at the time I needed to change who the owner and I just exported/imported & recreated them.  It was easy and what I knew would work.  After looking through the syntax for alter authorization I realized I could & probably should have done it this way.  I guess old habits die hard.

ALTER AUTHORIZATION ON CERTIFICATE::[MYCERTIFICATE] TO [CERTOWNER]

Some out there might question why I’m rehashing syntax changes that happened nearly 5 years ago, The reality is that even in 2011 a lot of the installations of SQL Server I look at are still 2005 and many of the people I run into still use old ways of doing things, this is for you (and me)!

ALTER AUTHORIZATION ON OBJECT::[Products].[Description] TO [AllenK]