
create login l1 with password = 'StrongPW1'
create login l2 with password = 'StrongPW2'
go

use
pubs
go

create user u1 for login l1
create user u2 for login l2
go

grant select on authors to u1, u2
go

execute as user='u1'
select * from dbo.authors
revert
go

execute as user='u2'
select * from dbo.authors
revert
go

-- should see one plan
SELECT sql.text, qs.EXECUTION_COUNT, qs.*, p.* 
FROM sys.dm_exec_query_stats AS qs 
CROSS APPLY sys.dm_exec_sql_text(sql_handle) sql
CROSS APPLY sys.dm_exec_query_plan(plan_handle) p
WHERE text NOT LIKE '%sql.text%'
ORDER BY qs.EXECUTION_COUNT DESC
go

-- see why the plans match
select * from sys.dm_exec_plan_attributes
(0x06000900DBE52F19B8A11A05000000000000000000000000)
go

-- this generates another plan user_id = -2
execute as user='u2'
select * from dbo.authors
revert
go

create schema u2schema authorization u2
go
alter user u2 with default_schema = u2schema
go

grant execute on schema::u2schema to public

-- this generates another plan user_id = u2schema_id
execute as user='u2'
select * from authors
revert
go

select * from sys.schemas