Sql-server – Issue with users creating objects in a user owned schema

The part that should interest you is the grants. But I figured a "code/picture" is worth a thousand words.

use master
go
create database test1
go
use test1
go
create schema data
go
create schema ref
go
create table data.tbl1(a int);insert data.tbl1 values(1);
go
create table ref.tbl2(a int);insert ref.tbl2 values(2);
go

create proc data.tbl1_select as select * from tbl1
go
create proc data.tbl1_insert(@a int) as insert tbl1 values(@a)
go
create proc data.tbl1_update(@a int) as update tbl1 set a = @a
go
create proc data.tbl1_delete as delete from tbl1
go

create proc ref.tbl2_select as select * from tbl2
go
create proc ref.tbl2_insert(@a int) as insert tbl2 values(@a)
go
create proc ref.tbl2_update(@a int) as update tbl2 set a = @a
go
create proc ref.tbl2_delete as delete from tbl2
go

create role users
go
create role admins
go
grant execute on schema::data to admins
grant execute on schema::ref to admins
grant execute on schema::data to users
grant execute on ref.tbl2_select to users
go

use master
go
create login user1 with password='', default_database=test1, check_expiration=off, check_policy=off
create login user2 with password='', default_database=test1, check_expiration=off, check_policy=off
go
use test1
go
create user user1 for login user1;
create user user2 for login user2;
alter role admins add member user1;
alter role users add member user2;
go

setuser 'user1'
go
exec ref.tbl2_update 3
exec ref.tbl2_select
exec data.tbl1_update 3
exec data.tbl1_select
go
setuser -- To go back to your user
go
setuser 'user2'
go
exec ref.tbl2_update 3
exec ref.tbl2_select
exec data.tbl1_update 3
exec data.tbl1_select
select * from ref.tbl2

Here's the result:

The EXECUTE permission was denied on the object 'tbl2_update', database 'test1', schema 'ref'.
The SELECT permission was denied on the object 'tbl2', database 'test1', schema 'ref'.

Short answer: Don't use db_datareader or db_datawriter or their deny equivalents. They are for backwards compatibility only. Using them will cause issues like the one you are facing.

If you want to give principal Alice the SELECT, INSERT, UPDATE and DELETE permissions to all table-valued objects in schema Sales then use the following.

GRANT SELECT, INSERT, UPDATE, DELETE ON SCHEMA::Sales TO Alice ;

If you want to give principal Alice the SELECT, INSERT, UPDATE and DELETE permissions to all table-valued objects in all schemas then use the following.

GRANT SELECT, INSERT, UPDATE, DELETE TO Alice ;

Metadata visibility will then work correctly.