patternsqlMinor

Changing Database Owner Removed User Mapping

Submitted by: @import:stackexchange-dba·Mar 10, 2026·

Viewed 0 times

ownerremoveduserdatabasemappingchanging

Problem

I ran an ALTER AUTHORIZATION to change a database owner from an AD account to sa and it seemed to cause the user mapping for a specific database to be removed. I’m not sure why this happened.

Is this normal and expected behaviour?

Solution

The database owner is mapped to the dbo user in the database, so when you change the database owner, you change which login is mapped to the dbo user.

The previous database owner may loose access to the database.

eg

use master
drop database mydatabase
go
create database mydatabase
go
use mydatabase
go
select sid
from sys.database_principals 
where principal_id = 1
go
alter authorization on database::mydatabase to sa
go
select sid
from sys.database_principals 
where principal_id = 1

outputs

sid
-------------------
0x0105000000000005. . . 

(1 row affected)

sid
-------------------
0x01

(1 row affected)

Code Snippets

use master
drop database mydatabase
go
create database mydatabase
go
use mydatabase
go
select sid
from sys.database_principals 
where principal_id = 1
go
alter authorization on database::mydatabase to sa
go
select sid
from sys.database_principals 
where principal_id = 1

sid
-------------------
0x0105000000000005. . . 

(1 row affected)

sid
-------------------
0x01

(1 row affected)

Context

StackExchange Database Administrators Q#238353, answer score: 3

Revisions (0)

No revisions yet.