SET XACT_ABORT, NOCOUNT ON
USE tempdb
go
IF db_id('Playground') IS NOT NULL
   DROP DATABASE Playground
go
CREATE DATABASE Playground
ALTER AUTHORIZATION ON DATABASE::Playground TO sa
go
USE Playground
go
CREATE ROLE Playrole
GRANT EXECUTE ON SCHEMA::dbo TO Playrole
CREATE USER Michelle WITHOUT LOGIN
ALTER ROLE Playrole ADD MEMBER Michelle
go
----------------------------------------------------------------------------------------------------
CREATE TABLE Playtable (id       int          NOT NULL,
                        somedata nvarchar(40) NOT NULL,
                        whodidit sysname      NOT NULL
                            CONSTRAINT Play_default_whodidit DEFAULT USER,
                        CONSTRAINT pk_Play PRIMARY KEY (id)
)
go
----------------------------------------------------------------------------------------------------
CREATE PROCEDURE add_playdata @id int, @somedata nvarchar(40) AS
   INSERT Playtable(id, somedata)
      VALUES(@id, @somedata)
   SELECT id, somedata, whodidit FROM Playtable WHERE id = @id
go
----------------------------------------------------------------------------------------------------
EXECUTE AS USER = 'Michelle'
INSERT Playtable(id, somedata, whodidit)
   VALUES (1, 'Des mots qui vont très bien ensamble', 'Maggie Mae')
go
REVERT
go
----------------------------------------------------------------------------------------------------
EXECUTE AS USER = 'Michelle'
EXEC add_playdata 1, 'Des mots qui vont très bien ensamble'
go
REVERT
go
----------------------------------------------------------------------------------------------------
CREATE USER NewOwner WITHOUT LOGIN
ALTER AUTHORIZATION ON Playtable TO NewOwner
go
EXECUTE AS USER = 'Michelle'
EXEC add_playdata 2, 'Words that go well together'
go
REVERT
go
----------------------------------------------------------------------------------------------------
ALTER AUTHORIZATION ON add_playdata TO NewOwner
go
EXECUTE AS USER = 'Michelle'
EXEC add_playdata 2, 'Words that go well together'
go
REVERT
go
----------------------------------------------------------------------------------------------------
ALTER AUTHORIZATION ON Playtable TO SCHEMA OWNER
ALTER AUTHORIZATION ON add_playdata TO SCHEMA OWNER
go
----------------------------------------------------------------------------------------------------
CREATE PROCEDURE truncate_playdata AS
   TRUNCATE TABLE Playtable
go
EXECUTE AS USER = 'Michelle'
EXEC truncate_playdata
go
REVERT
go
----------------------------------------------------------------------------------------------------
CREATE PROCEDURE dynamic_playdata @searchstr nvarchar(MAX) AS
   DECLARE @sql nvarchar(MAX)
   SELECT @sql = 'SELECT * FROM dbo.Playtable
                  WHERE somedata LIKE ''%'' + ''' + @searchstr + ''' + ''%'''
   EXEC(@sql)
go
EXECUTE AS USER = 'Michelle'
EXEC dynamic_playdata 'ensamble'
go
REVERT
go
-----------------------------------------------------------------------------------------------------
CREATE PROCEDURE check_playdata @id int AS
   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_playdata 1
go
REVERT
go
------------------------------------------------------------------------------------------------------
CREATE SCHEMA Reports
go
CREATE ROLE DevRole
CREATE USER MrKite WITHOUT LOGIN
ALTER ROLE DevRole ADD MEMBER MrKite
go
GRANT CREATE PROCEDURE TO DevRole
GRANT ALTER ON Schema::Reports TO DevRole
ALTER ROLE db_datareader ADD MEMBER DevRole
go
-------------------------------------------------------------------------------------------------------
EXECUTE AS USER = 'MrKite'
go
CREATE PROCEDURE testrep AS PRINT 11
go
REVERT
go
--------------------------------------------------------------------------------------------------------
EXECUTE AS USER = 'MrKite'
go
CREATE PROCEDURE Reports.test_report @id int AS 
   SELECT id, somedata, whodidit FROM dbo.Playtable WHERE id = @id
go
EXEC Reports.test_report 1
go
REVERT
go
-------------------------------------------------------------------------------------------------------
ALTER AUTHORIZATION ON SCHEMA::Reports TO DevRole
go
EXECUTE AS USER = 'MrKite'
EXEC Reports.test_report 1
go
REVERT