In one server SQL Server 2005 when I tried to open the properties of the database using the GUI I saw:

Indeed, we cannot determine the database owner:

select owner_sid, suser_sname(owner_sid) from sys.databases where name='model'
owner_sid owner name
0x010500000000000515000000B92ACD62A473F62D825A8A49A3780000 NULL

It was probably the domain user that no longer exists in AD.
Change the owner with procedure sp_changedbowner in the database model is prohibited::

use model; EXEC sp_changedbowner 'sa'

Msg 15109, Level 16, State 1, Line 1
Cannot change the owner of the master, model, tempdb or distribution database.

We can change the sid directly in the system table, as described here, but it is, really, is not recommended by MS way.

So we use the fact that owner of any attached database become the current user, and running SQL Server with the key /T3608, execute under the user’s session sa:

EXEC master.dbo.sp_detach_db @dbname = N'model'
GO
CREATE DATABASE [model] ON
( FILENAME = N'E:\DATA\model.mdf' ),
( FILENAME = N'E:\DATA\modellog.ldf' )
FOR ATTACH
GO

Restart the SQL Server, we see:

select owner_sid, suser_sname(owner_sid) 'owner name' from sys.databases where name='model'
owner_sid owner name
0x01 sa

Leave a Reply

Your email address will not be published. Required fields are marked *

October 2019
M T W T F S S
« Jul    
 123456
78910111213
14151617181920
21222324252627
28293031