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 *

September 2021
M T W T F S S
« Jul    
 12345
6789101112
13141516171819
20212223242526
27282930