| use master GO create database ExecutionContextDB GO create login BarneyLogin with password='1GoodPassword' create login FredLogin with password='2GoodPassword' create login WilmaLogin with password='3GoodPassword' GO use ExecutionContextDB GO --Create our database users mapped to their login and default schema create user BarneyUser for login BarneyLogin with default_schema=BarneySchema create user FredUser for login FredLogin with default_schema=FredSchema create user WilmaUser for login WilmaLogin with default_schema=WilmaSchema GO --Create our schemas for each user create schema BarneySchema authorization BarneyUser GO create schema FredSchema authorization FredUser GO create schema WilmaSchema authorization WilmaUser GO --Create a table that Barney's schema owns create table BarneySchema.RockHits ( YearPublished int NOT NULL, Title nvarchar(50) NOT NULL ) GO --Insert some data into the table insert into BarneySchema.RockHits values('1960','Pebbles Jam') insert into BarneySchema.RockHits values('1961','Dino Disco') insert into BarneySchema.RockHits values('1961','Fred''s Dance Formula') GO GRANT SELECT ON BarneySchema.RockHits to FredUser --Create the stored procedure that Fred's Schema owns --The stored proc executes under whomever is calling it using EXECUTE AS CALLER create procedure FredSchema.ListHits @Year int WITH EXECUTE AS CALLER AS BEGIN select CURRENT_USER as '(Execute as Caller), Current User Context=' select YearPublished,Title from BarneySchema.RockHits where YearPublished=@Year END --Let's grant Wilma the ability to execute this stored proc GRANT EXECUTE ON FredSchema.ListHits to WilmaUser --At this point we can begin playing with context switching --We have given Fred access to Barney's table of hits --We have given Wilma access to Fred's Stored Proc --Let’s begin by logging in as Fred and seeing if this stored proc works execute as user='FredUser' GO exec ListHits 1961 --go back to sysadmin REVERT GO EXECUTE AS user='WilmaUser' GO exec FredSchema.ListHits 1961 --We get the SELECT permission denied error as expected --because the stored proc is executing as WilmaUser --Now let's ALTER the stored procedure so that it will run under --its owner, Fred. REVERT GO ALTER PROCEDURE FredSchema.ListHits @Year int WITH EXECUTE AS OWNER AS BEGIN select CURRENT_USER as '(Execute as Owner), Current User Context=' select YearPublished,Title from BarneySchema.RockHits where YearPublished=@Year END GO --Now let's try Wilma again EXECUTE AS user='WilmaUser' GO exec FredSchema.ListHits 1961 --As you can see the current user context is FredUser! and we didn't --have to give Wilma explicit permissions to the underlying table in --Fred's stored proc. REVERT GO |