Day 9: Error running sp_helpdb

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.

Resolution:-

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’;

Or

ALTER AUTHORIZATION ON DATABASE::MyDatabase to sa;

Reference:

http://weblogs.sqlteam.com/dang/archive/2008/01/13/Database-Owner-Troubles.aspx

Remember :

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.

http://msdn.microsoft.com/en-us/library/aa259622(SQL.80).aspx

Advertisements
This entry was posted in TempDB, Troubleshooting and tagged , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s