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.