-- Create the test environment.  
USE master
GO
CREATE DATABASE test
GO
USE test
GO
CREATE LOGIN TestUser WITH PASSWORD = 'Password';
GO
CREATE USER TestUser
GO
CREATE SCHEMA secret
GO
CREATE SCHEMA visible
GO
CREATE TABLE secret.t (c INT);
Go
INSERT INTO secret.t VALUES (42);
GO
DENY SELECT ON secret.t TO TestUser;
GRANT ALTER ON SCHEMA::visible TO TestUser;
GO
--########
-- Scenario 1
-- Grant permissions.
GRANT EXECUTE ON SCHEMA::visible TO TestUser;
GRANT CREATE PROCEDURE TO TestUser;
GO
-- Access the denied object.
EXECUTE AS USER = 'TestUser';
SELECT USER_NAME() AS CURRENT_USER_NAME;
GO
CREATE PROCEDURE visible.sptest AS
BEGIN
SELECT * FROM secret.t
END
GO
SELECT 'Scenario 1: Executing procedure'
EXEC visible.sptest
GO

-- Scenario 2
-- Clear the state.
REVERT
GO
DROP PROCEDURE visible.sptest
REVOKE EXECUTE ON SCHEMA::visible TO TestUser;
REVOKE CREATE PROCEDURE TO TestUser;
GO
-- Grant permissions.
GRANT SELECT ON SCHEMA::visible TO TestUser;
GRANT CREATE SYNONYM TO TestUser;
GO
-- Access the denied object.
EXECUTE AS USER = 'TestUser';
SELECT USER_NAME() AS CURRENT_USER_NAME;
GO
CREATE SYNONYM visible.mytest
FOR secret.t;
GO
SELECT 'Scenario 2: Querying from the synonym'
SELECT * FROM visible.mytest
GO
-- Scenario 3
-- Clear the state.
REVERT
GO
DROP SYNONYM visible.mytest
REVOKE SELECT ON SCHEMA::visible TO TestUser;
REVOKE CREATE SYNONYM TO TestUser;
GO
-- Grant permissions.
GRANT SELECT ON SCHEMA::visible TO TestUser;
GRANT CREATE VIEW TO TestUser;
GO
-- Access the denied object.
EXECUTE AS USER = 'TestUser';
SELECT USER_NAME() AS CURRENT_USER_NAME;
GO
CREATE VIEW visible.myview
AS
SELECT * FROM secret.t
GO

SELECT 'Scenario 3: Querying from the newly created view'
SELECT * FROM visible.myview

-- Remove the test database.
REVERT
GO
USE master
GO
DROP LOGIN TestUser
GO
DROP DATABASE test
GO