SET NOCOUNT, XACT_ABORT ON
USE master
go
CREATE DATABASE BulkPlay
DECLARE @pwd char(37) = convert(char(36), newid()) + 'a'
DECLARE @sql nvarchar(MAX)
SELECT @sql = 'CREATE LOGIN BulkPlay$owner ' +
              'WITH PASSWORD = ' + quotename(@pwd, '''')
EXEC(@sql)
ALTER LOGIN BulkPlay$owner DISABLE
DENY CONNECT SQL TO BulkPlay$owner
ALTER AUTHORIZATION ON DATABASE::BulkPlay TO BulkPlay$owner
go
CREATE LOGIN ERigby WITH PASSWORD = 'Look at the all the lonely people'
go 
-----------------------------------------------------------------------------------------------------
EXEC xp_cmdshell 'ECHO 1;The Fool on the Hill;> C:\temp\bulktest.txt' 
EXEC xp_cmdshell 'ECHO 2;The Ballad of John and Yoko;>> C:\temp\bulktest.txt'
go
-----------------------------------------------------------------------------------------------------
USE BulkPlay
go
CREATE ROLE BulkRole
CREATE USER ERigby
ALTER ROLE BulkRole ADD MEMBER ERigby
GRANT EXECUTE ON SCHEMA::dbo TO BulkRole
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, 'Rubber Soul')
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
   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')
EXEC Management.GrantPermsToSP 'reload_table', @perms, @debug = 1
go
------------------------------------------------------------------------------------------------------
EXECUTE AS LOGIN = 'ERigby'
EXEC reload_table
go
REVERT
go
------------------------------------------------------------------------------------------------------
/* Change in GrantPermsToSP_server.sql:
DECLARE @database nvarchar(260) = 'BulkPlay',
        @procname nvarchar(520) = 'reload_table'

DECLARE @perms TABLE (perm nvarchar(400) NOT NULL PRIMARY KEY)
INSERT @perms VALUES ('bulkadmin')
*/
go
------------------------------------------------------------------------------------------------------
EXECUTE AS LOGIN = 'ERigby'
EXEC reload_table
go
REVERT
go
------------------------------------------------------------------------------------------------------
SELECT * FROM Bulktable
go
------------------------------------------------------------------------------------------------------
ALTER 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
------------------------------------------------------------------------------------------------------
/* GrantPermsToSP_server.sql:
DECLARE @database nvarchar(260) = 'BulkPlay',
        @procname nvarchar(520) = 'reload_table'

DECLARE @perms TABLE (perm nvarchar(400) NOT NULL PRIMARY KEY)
INSERT @perms VALUES ('bulkadmin')
*/
-------------------------------------------------------------------------------------------------------
EXECUTE AS LOGIN = 'ERigby'
EXEC reload_table
go
REVERT
go
-------------------------------------------------------------------------------------------------------
-- Cleanup, do not run if you plan to do the Service Broker lab.
USE master
go
ALTER DATABASE BulkPlay SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DROP DATABASE BulkPlay
go
DROP LOGIN ERigby
go
EXEC xp_cmdshell 'DEL C:\temp\bulktest.txt'
go
DROP LOGIN "SIGN [BulkPlay].[dbo].[reload_table]"
go
DROP LOGIN BulkPlay$owner
go
DROP CERTIFICATE "SIGN [BulkPlay].[dbo].[reload_table]"