--THIS DEMO SHOWCASES MODULE EXECUTION CONTEXT 

--create the database for this demo
use master
DROP DATABASE exec_context_db
CREATE DATABASE exec_context_db
use exec_context_db

--create the users 
CREATE USER TEST1 FOR LOGIN TEST1 
WITH DEFAULT_SCHEMA = PrivateSchema
CREATE USER TEST2 FOR LOGIN TEST2 
WITH DEFAULT_SCHEMA = PublicSchema
CREATE USER TEST3 FOR LOGIN TEST3 
WITH DEFAULT_SCHEMA = PublicSchema
--create the schemas 
CREATE SCHEMA PublicSchema AUTHORIZATION TEST1
go
CREATE SCHEMA PrivateSchema AUTHORIZATION TEST1
--create a table and a proc in different schemas 
--to ensure that there is no ownerhsip chaining

--create the table in Privateschema
CREATE TABLE PrivateSchema.VendorTable (ID int, name varchar(50), state char(2), phno char(12))
Insert into PrivateSchema.VendorTable values (1,'Vendor1','CA','123-345-1232')
Insert into PrivateSchema.VendorTable values (2,'Vendor2','WA','454-765-3233')
Insert into PrivateSchema.VendorTable values (3,'Vendor3','TX','345-776-3433')
Insert into PrivateSchema.VendorTable values (4,'Vendor4','CA','232-454-5654')
Insert into PrivateSchema.VendorTable values (5,'Vendor5','TX','454-545-5654')
Insert into PrivateSchema.VendorTable values (6,'Vendor6','IN','232-655-1232')
Insert into PrivateSchema.VendorTable values (7,'Vendor7','IN','453-454-1232')
Insert into PrivateSchema.VendorTable values (8,'Vendor8','WA','555-654-1232')
Insert into PrivateSchema.VendorTable values (9,'Vendor9','CA','555-345-1232')
--create the proc in test2schema
CREATE PROC PublicSchema.VendorAccessProc @state char(2)
as 
exec ('select * from PrivateSchema.VendorTable 
where state = '''+ @state +'''')
--grant permissions on the proc
grant execute on PublicSchema.VendorAccessProc to test3
--now try and access the proc as test3
--...notice name resolution hitting the default schema
execute as user = 'test3'
exec PublicSchema.VendorAccessProc 'CA'

--you see the permission is denied on teh underlying table. 
--But I do not want to have to grant permissions to all
-- callers on teh underlying table
--instead I grant permissions on the table to one user
--and have the proc run as that user.
revert
grant select on PrivateSchema.VendorTable to test2
--now have a proc with test2 as the execution context
create proc PublicSchema.VendorAccessProc2 @state char(2)
WITH EXECUTE AS 'test2'
AS 
begin
if @state = 'CA' or @State = 'WA' or @State = 'IN'or @State = 'TX'
exec ('select * from PrivateSchema.VendorTable where state ='''+ @state+ '''')
else
print 'invalid input'
end
--grant permissions on this proc to test3
grant execute on PublicSchema.VendorAccessProc2 to test3
--now try and execute the proc as test3
execute as user = 'test3'

exec PublicSchema.VendorAccessProc2 'CA'
revert
--or alter the first proc to do the same thing
ALTER PROC PublicSchema.VendorAccessProc @state char(2)
with execute as 'test2'
as exec ('select * from PrivateSchema.VendorTable where state = '''+ @state +'''')
go
--now try and execute the proc as test3
execute as user = 'test3'
exec PublicSchema.VendorAccessProc 'IN'
revert

