Tuesday, February 14, 2012

Change owner user defined datatype

Hello, We had a developer that created some user defined data types. He is no longer with our company and we want to change owner of those user defined data types so we will be able to delete his UUID. Is their a way to change the owner of the user defined data types from his ID to dbo. I don't see a way to change them with sp_changeobjectowner

Thanks in advance
Jef WainHere is the example, this code will change user defined datatype's owner from 'test' to 'dbo' which created by 'test' user

exec sp_configure 'allow updates', '1'

reconfigure WITH OVERRIDE

go

BEGIN TRANSACTION

update systypes set uid = user_id('dbo') where uid = user_id('test')

COMMIT TRANSACTION

exec sp_configure 'allow updates', '0'

reconfigure WITH OVERRIDE

go|||Thank you very much|||sp_changeobjectowner (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sp_ca-cz_1lpu.asp) will also do this, in a way that is supported by Microsoft.

-PatP

No comments:

Post a Comment