SET NOCOUNT, XACT_ABORT ON
USE master
EXEC sp_configure 'contained database authentication', 1
RECONFIGURE
go
---------------------------------------------------------------------------------------------------
CREATE DATABASE PlayContainer CONTAINMENT = PARTIAL
DECLARE @pwd char(37) = convert(char(36), newid()) + 'a'
DECLARE @sql nvarchar(MAX)
SELECT @sql = 'CREATE LOGIN PlayContainer$owner ' +
              'WITH PASSWORD = ' + quotename(@pwd, '''')
EXEC(@sql)
ALTER LOGIN PlayContainer$owner DISABLE
DENY CONNECT SQL TO PlayContainer$owner
ALTER AUTHORIZATION ON DATABASE::PlayContainer TO PlayContainer$owner
go
-----------------------------------------------------------------------------------------------------
EXEC xp_cmdshell 'ECHO 1;Please, Please Me;> C:\temp\bulktest.txt' 
EXEC xp_cmdshell 'ECHO 2;Old Brown Shoe;>> C:\temp\bulktest.txt'
go
-----------------------------------------------------------------------------------------------------
USE PlayContainer
go
CREATE ROLE ContainerRole
CREATE USER McKenzie WITH PASSWORD = 'No one was saved'
ALTER ROLE ContainerRole ADD MEMBER McKenzie
GRANT EXECUTE ON SCHEMA::dbo TO ContainerRole
go
CREATE TABLE Bulktable(id       int          NOT NULL,
                       somedata varchar(40)  NOT NULL,
                       loadtime datetime2(3) NOT NULL
                          CONSTRAINT def_Bulktable_loadtime DEFAULT sysdatetime(),
                       CONSTRAINT pk_Bulktable PRIMARY KEY (id)
)
INSERT Bulktable(id, somedata) VALUES (1, 'Yesterday')
go
-----------------------------------------------------------------------------------------------------
CREATE PROCEDURE reload_table AS
   SELECT name, usage, type FROM sys.login_token
   SELECT name, usage, type FROM sys.user_token
   TRUNCATE TABLE Bulktable
   EXEC ('BULK INSERT Bulktable FROM ''C:\temp\bulktest.txt''
          WITH (DATAFILETYPE = ''char'',
                FIELDTERMINATOR = '';'')')
   SELECT id, somedata, loadtime FROM Bulktable ORDER BY id
go
-----------------------------------------------------------------------------------------------------
DECLARE @perms Management.Permission_list
INSERT @perms VALUES('ALTER ON Bulktable'),
                    ('INSERT ON Bulktable')
EXEC Management.GrantPermsToSP 'reload_table', @perms, @debug = 1
go
-----------------------------------------------------------------------------------------------------
/* Open GrantPermsToSP_server.sql and change the parameter part to read:
DECLARE @database nvarchar(260) = 'PlayContainer',
        @procname nvarchar(520) = 'reload_table'

DECLARE @perms TABLE (perm nvarchar(400) NOT NULL PRIMARY KEY)
INSERT @perms VALUES ('ADMINISTER BULK OPERATIONS')
*/
go
------------------------------------------------------------------------------------------------------
EXECUTE AS USER = 'McKenzie'
EXEC reload_table
go
REVERT
go
-----------------------------------------------------------------------------------------------------
USE master
go
ALTER DATABASE PlayContainer SET TRUSTWORTHY ON 
GRANT AUTHENTICATE SERVER TO PlayContainer$owner
go
USE PlayContainer
EXECUTE AS USER = 'McKenzie'
EXEC reload_table
go
REVERT
go
-------------------------------------------------------------------------------------------------------
USE master
go
ALTER DATABASE PlayContainer SET TRUSTWORTHY OFF
REVOKE AUTHENTICATE SERVER TO PlayContainer$owner
GRANT AUTHENTICATE SERVER TO "SIGN [PlayContainer].[dbo].[reload_table]"
go
--------------------------------------------------------------------------------------------------------
USE PlayContainer
EXECUTE AS USER = 'McKenzie'
EXEC reload_table
go
REVERT
go
--------------------------------------------------------------------------------------------------------
CREATE USER reload_table$proxy WITHOUT LOGIN
GRANT INSERT, ALTER ON Bulktable TO reload_table$proxy
go
ALTER PROCEDURE reload_table WITH EXECUTE AS 'reload_table$proxy' AS
   SELECT name, usage, type FROM sys.login_token
   SELECT name, usage, type FROM sys.user_token
   TRUNCATE TABLE Bulktable
   EXEC ('BULK INSERT Bulktable FROM ''C:\temp\bulktest.txt''
          WITH (DATAFILETYPE = ''char'',
                FIELDTERMINATOR = '';'')')
   SELECT id, somedata, loadtime FROM Bulktable ORDER BY id
go
--------------------------------------------------------------------------------------------------------
/* GrantPermsToSP_server.sql:
DECLARE @database nvarchar(260) = 'PlayContainer',
        @procname nvarchar(520) = 'reload_table'

DECLARE @perms TABLE (perm nvarchar(400) NOT NULL PRIMARY KEY)
INSERT @perms VALUES
   ('ADMINISTER BULK OPERATIONS'),
   ('AUTHENTICATE SERVER')
*/
--------------------------------------------------------------------------------------------------------
EXECUTE AS USER = 'McKenzie'
EXEC reload_table
go
REVERT
go
-------------------------------------------------------------------------------------------------------
ALTER USER reload_table$proxy WITH LOGIN = somelogin
go
-------------------------------------------------------------------------------------------------------
USE master
go
ALTER DATABASE PlayContainer SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DROP DATABASE PlayContainer
go
EXEC xp_cmdshell 'DEL C:\temp\bulktest.txt'
go
DROP LOGIN "SIGN [PlayContainer].[dbo].[reload_table]"
go
DROP LOGIN PlayContainer$owner
go
DROP CERTIFICATE "SIGN [PlayContainer].[dbo].[reload_table]"
go
EXEC sp_configure 'contained database authentication', 0
RECONFIGURE