Sp_helpdb is very helpful command for sql server 2000, which give information about sql server databases, so time we get an error as
Cannot insert the value NULL into column ‘owner’, table ‘tempdb.dbo.#xxx__________________000’; column does not allow nulls. INSERT fails.
When we restored or attached the database from other server, sometimes the database owner will be NULL due to SID out of sync or orphan SID.
Check the databases which has owner NULL using sysdatabases or sys.databases and change the owner to “sa”
SELECT name, SUSER_SNAME(sid) FROM sysdatabases
EXEC MyDatabase..sp_changedbowner ‘sa’;
ALTER AUTHORIZATION ON DATABASE::MyDatabase to sa;
we cannot change owner of “master, model, tempdb and distribution” databases
Sp_changedbowner “sa” is not allowed on some databases – master, model and tempdb and distribution.
If we try we get below error:
Msg 15109, Level 16, State 1, Procedure sp_changedbowner, Line 22
Cannot change the owner of the master, model, tempdb or distribution database.