Users with role still able to INSERT despite not having a right to do so

Question

I have a following requirement: There is a AAD group called testgroup. Members of this group should have write access to table dbo2.Sales and read access to every table in dbo schema, and they shouldn't be able to do anything else.

To do this, I created a user for this AAD group and a role:

create user testgroup from external provider;
create role test_role;
alter role test_role add member testgroup 

Database has multiple schemas, so I deny access to most of them:

DECLARE @schemaName sysname;
DECLARE cur CURSOR FOR SELECT name FROM sys.schemas where name not in ('dbo', 'dbo2', 'sys', 'INFORMATION_SCHEMA')
OPEN cur

FETCH NEXT FROM cur INTO @schemaName
WHILE @@FETCH_STATUS = 0 BEGIN
    print  ('deny control on schema::' + @schemaName +' to test_role')
    exec  ('deny control on schema::' + @schemaName +' to test_role')
    FETCH NEXT FROM cur INTO @schemaName
END

CLOSE cur    
DEALLOCATE cur

After that I allow access to needed schema and table:

grant select,delete,insert,update on object::dbo2.Sales to test_role
grant select on schema::dbo to test_role

This seems to work fine, except for the fact that users can INSERT to dbo tables.

testgroup only has 2 roles: test_role and public.

How do I troubleshoot this?

Edit on what I tried so far:

running sp_table_privileges returns correct results: EXEC sp_table_privileges @table_name = 'Sales'; shows delete, insert, update and select priveleges with testgroup as grantee.

EXEC sp_table_privileges @table_name = 'Tenant'(which is a table from dbo schema) shows no priveleges with testgroup as grantee.

SELECT 
    class_desc 
  , CASE WHEN class = 0 THEN DB_NAME()
         WHEN class = 1 THEN OBJECT_NAME(major_id)
         WHEN class = 3 THEN SCHEMA_NAME(major_id) END [Securable]
  , USER_NAME(grantee_principal_id) [User]
  , permission_name
  , state_desc
FROM sys.database_permissions
where USER_NAME(grantee_principal_id) = 'test_role'

also shows that only INSERT permission is for Sales table.

Answer 1

To troubleshoot this I would start by impersonating a login (server-level principal) with the permission issues.

EXECUTE AS LOGIN = 'LoginInTestRole'

and running these two statements in the context of the correct database:

SELECT * FROM sys.user_token AS ut 
SELECT * FROM sys.login_token AS lt

This should tell you if there aren't any unexpected roles/groups that the login is part of.

Don't forget to revert the impersonation when you're done