if object_id( 'SchemaA.TableA' ) is not null drop table SchemaA.TableA
if object_id( 'SchemaB.TableA' ) is not null drop synonym SchemaB.TableA
if schema_id( 'SchemaA' ) is not null drop schema SchemaA
if schema_id( 'SchemaB' ) is not null drop schema SchemaB
if user_id( 'UserB' ) is not null drop user UserB
go
create schema SchemaA
go
create table SchemaA.TableA ( id int identity(1,1), name varchar(20), systemuser sysname default system_user, sessionuser sysname default session_user, currentuser sysname default current_user )
go
create schema SchemaB
go
create synonym SchemaB.TableA for SchemaA.TableA
go
create user UserB without login with default_schema = SchemaB
go
grant DELETE, INSERT, REFERENCES, SELECT, UPDATE on schema::SchemaB to UserB
go
insert into SchemaB.TableA( name ) values ( 'Test1' )
go
execute as user = 'UserB'
go
insert into SchemaB.TableA( name ) values ( 'Test2' )
select * from SchemaA.TableA
select * from SchemaB.TableA
go
revert
go