Can not remove user from db_owner-group-CodePudding

TL;DR - Users automatically get added to db_owner group when created by script for some reason. But I can't remove them with a script.

So I have made a script to add new Windows-users to my database but for some reason they automatically get added to the db_owner group by default. The owner on the server is 'sa' so there is an owner set already on the server.

This is what happens when I add a new user with the following script:

CREATE LOGIN [Windows\user1] FROM WINDOWS WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english]
GO

USE [dbName]
GO
IF NOT EXISTS (SELECT [name] FROM [sys].[database_principals] WHERE [name] = N'Windows\user1')
BEGIN
    CREATE USER [Windows\user1] FOR LOGIN [Windows\user1]
END
EXECUTE sp_addrolemember 'Admin', 'Windows\user1'
GO
EXECUTE sp_addrolemember 'db_datareader', 'Windows\user1'
GO
EXECUTE sp_addrolemember 'db_datawriter', 'Windows\user1'
GO

But when I run the following code it says "1 row affected". But the user is still in the db_owner group.

ALTER ROLE [db_owner] DROP MEMBER [Windows\user1]

The only way I can remove it right now is using the UI in SSMS to go to the properties of the user and un-check it. What are some ways to fix either of the issues?

Apparently this wasn't correct. They don't even get removed this way...

Update(1): I've managed to narrow it down to the fact that it does seem to have something to do with the custom role "Admin" - when a user is added to this role it automatically gets added to db_owner when running the following script.

EXECUTE sp_addrolemember 'Admin', 'Windows\user1'

Still haven't managed to figure out why.

CodePudding user response：

Prediction: if you run the following query, then the result will be 1:

select   count(*)
from     sys.database_role_members   rm
join     sys.database_principals     rp   on rp.principal_id = rm.role_principal_id
join     sys.database_principals     mp   on mp.principal_id = rm.member_principal_id
where    rp.name = 'db_owner'
         and mp.name = 'Admin';

If that is the case (and I am confident that it is), then the Admin role is a member of the db_owner role. So all members of admin are members of db_owner.

Then user1 is given membership in the admin role. We now form a syllogism:

All admins are db_owners.
user1 is an admin.
Therefore user1 is a db_owner.

Attempting to remove user1 from the db_owner role has no effect, because they are not a direct member of the db_owner role. They are a member of the admin role, which in turn is a member of the db_owner role.

If you want to remove their db_owner membership, you must do so by removing them from the admin role.