Accidentally assign db_owner to a Domain User & unable to change Ownership

Question

Accidentally assign db_owner to a Domain User & unable to change Ownership

TonyJK 881

Hi,

We are using SQL Serve 2019 and accidentally granted db_owner to a newly created domain user. We are not aware until we find that he can full access right to the database instead of read only.

Then we dropped that Login. However, it seems that it is not successful as we get "Drop member failed for DatabaseRole 'db_owner'. (Microsoft.SqlServer.Smo)".

We tried to change database owner to "sa" but we get the following error.

Set owner failed for Database 'FinanceProd'. (Microsoft.SqlServer.Smo)

We get similar error when we try to change dbo

EXEC sp_changedbowner 'Domain\Administrator';

Is there any suggestion ?

Thanks a lot

TonyJK 881 Reputation points

2023-02-17T09:09:20.7233333+00:00
I have tried the following query and it is empty

USE FinanceProd; SELECT s.name FROM sys.schemas s WHERE s.principal_id = USER_ID('Domain\Peter');

Accepted answer

1 additional answer

Your answer

TonyJK 881 Reputation points

2023-02-17T09:09:20.7233333+00:00

I have tried the following query and it is empty

USE FinanceProd; SELECT s.name FROM sys.schemas s WHERE s.principal_id = USER_ID('Domain\Peter');

Answer 1

Erland Sommarskog 121.4K MVP Volunteer Moderator

To drop the user from db_owner do:

ALTER ROLE db_owner DROP MEMBER "Domain\User"

But it is very clear from your post what you have actually done. Why did you try to change the database owner to sa?

The query against sys.schemas has no relevance in this context.

TonyJK 881 Reputation points

2023-02-18T04:48:51.25+00:00
Dear Erland,

May I ask

Is there reason why Domain\Peter is the Database Owner ?

Usually, we set "sa" as Database Owner. Is there any other better alternative ?

Thanks again
Erland Sommarskog 121.4K Reputation points MVP Volunteer Moderator

2023-02-18T10:12:06.68+00:00
In your original post, you said that you granted this Peter membership in db_owner. db_owner is a fixed database role, and membership in this role gave him rights to do anything in the database. However, it does not make him owner of the database.

It seems now actually made this user a owner of the database. We can only work from the information that you share, and we more or less has to assume that it is accurate.

Is there reason why Domain\Peter is the Database Owner ?

That would be because someone made him the owner. Which is not a good idea. You don't want a database to be owned by a real person who can leave the company and fall out of the AD. That will cause trouble.

Usually, we set "sa" as Database Owner. Is there any other better alternative ?

Yes. Best practice in my opinion is that a database should be owned by an SQL login that exists solely to own that database, and which has been granted no other permissions. Here is an example of how to achieve this for an existing database:

DECLARE @sql nvarchar(MAX) SELECT @sql = concat( 'CREATE LOGIN AnotherDB$owner WITH PASSWORD = ''', newid(), '''') EXEC(@sql) ALTER LOGIN AnotherDB$owner DISABLE ALTER AUTHORIZATION ON DATABASE::AnotherDB TO AnotherDB$owner

The dollar character does not mean anything special, it is just a character that is permitted in identifiers. That's all.

Answer 2

LiHongMSFT-4306 31,566

Hi @TonyJK

Did you drop that user from assigned Database? Otherwise, when you create new user with same name it would give such error.

Best regards,

Cosmog Hong

TonyJK 881 Reputation points

2023-02-17T21:03:59.8266667+00:00

Yes I did. Is there any way for us to rectify the issue ?

Besides, when we create the user, even though giving him db_owner role, is there any reason it changes owner from Domain\Administrator ?

Thanks
TonyJK 881 Reputation points

2023-02-17T23:09:14.26+00:00

It is pretty weird as the database owner has somehow changed to "sa" over the night.

We have deleted that SQL Login from Database Server but still find that it can access the database !?

Share via

Accidentally assign db_owner to a Domain User & unable to change Ownership

1 additional answer

Your answer