Wednesday 20 April 2011

ASE : How to change tempdb ownership

When I created second tempdb using my own DBA id due to SOX compliance, the db was under my ID. Now I need to change the ownership to sa. Sybase doc says you can change ownership with sp_changedbowner, but that's not right. and Sybase needs to update the doc to avoid too many support calls.

Here is how to change, I tested it, it works perfectly.

1>use master
2>go
1>sp_configure 'allow updates', 1
2>go
1>begin tran
2>go
1>update sysdatabases set suid = suser_id("") where name = ""
2>go
1>update ..sysusers set suid = suser_id("1>owner's name") where suid=
2>go
1>dbcc dbrepair(, "updowner")
2>go
1>select suid from master..sysdatabases where name = 1>tempdb>
2>go
1>select * from ..sysusers where name = ''
2>go
1>commit tran
2>go

*****If the data from the selects do not look right, then issue a rollback tran instead of a commit tran

1>sp_configure 'allow updates', 0
2>go

By running the dbcc dbrepair command you should not have to recycle the ASE.

No comments:

Post a Comment