SET NOCOUNT, XACT_ABORT ON
USE BulkPlay
go
CREATE TABLE debug_table (data    char(2)       NOT NULL,
                          token   nvarchar(128) NOT NULL,
                          usage   nvarchar(40)  NULL,
                          type    nvarchar(40)  NULL
)
go
ALTER PROCEDURE reload_table AS
   INSERT debug_table (data, token, usage, type)
      SELECT 'LT', name, usage, type FROM sys.login_token
   INSERT debug_table (data, token, usage, type)
      SELECT 'UT', name, usage, type FROM sys.user_token
   TRUNCATE TABLE Bulktable
   EXEC ('BULK INSERT Bulktable FROM ''C:\temp\bulktest.txt''
          WITH (DATAFILETYPE = ''char'',
                FIELDTERMINATOR = '';'')')
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')
*/
go
------------------------------------------------------------------------------------------------------
CREATE MESSAGE TYPE FileImport VALIDATION = NONE
CREATE CONTRACT FileImportContract (FileImport SENT BY INITIATOR)  
go
CREATE PROCEDURE FileImportFromQueue AS PRINT 1
go
CREATE QUEUE ReceiverQueue
WITH   STATUS = ON, 
       ACTIVATION (STATUS = ON, 
                   PROCEDURE_NAME = dbo.FileImportFromQueue, 
                   MAX_QUEUE_READERS = 1, 
                   EXECUTE AS OWNER)
go
CREATE QUEUE SenderQueue WITH STATUS = ON
go
CREATE SERVICE ReceiverService ON QUEUE dbo.ReceiverQueue (FileImportContract)
CREATE SERVICE SenderService ON QUEUE dbo.SenderQueue (FileImportContract)
go
--------------------------------------------------------------------------------------------------------
ALTER PROCEDURE FileImportFromQueue AS
SET XACT_ABORT, NOCOUNT ON
   DECLARE  @dialog  uniqueidentifier,
            @msgtype sysname

   ;RECEIVE TOP(1) @dialog  = conversation_handle,
                   @msgtype = message_type_name
   FROM ReceiverQueue

   IF @msgtype = 'FileImport'
   BEGIN
      BEGIN TRY
         EXEC reload_table
      END TRY
      BEGIN CATCH
         INSERT debug_table(data, token)
            VALUES ('E', substring(error_message(), 1, 128))
      END CATCH
   END
   
   END CONVERSATION @dialog
go
-----------------------------------------------------------------------------------------------------------
CREATE PROCEDURE FileImportInitiate AS
   SET XACT_ABORT, NOCOUNT ON

   DECLARE @dialog   uniqueidentifier,
           @msgbody  xml

   DELETE debug_table

   BEGIN DIALOG @dialog
   FROM SERVICE SenderService
   TO SERVICE   'ReceiverService', 'CURRENT DATABASE' 
   ON CONTRACT  FileImportContract
   WITH ENCRYPTION = OFF, LIFETIME = 3600 

   ;SEND ON CONVERSATION @dialog MESSAGE TYPE FileImport (0x) 

   WAITFOR (RECEIVE TOP (1) @msgbody = message_body
            FROM    SenderQueue), TIMEOUT 4000

   END CONVERSATION @dialog

   --SELECT CAST(@msgbody AS xml) AS response
   SELECT data, token, usage, type
   FROM   debug_table

   SELECT id, somedata, loadtime FROM Bulktable ORDER BY id
go
---------------------------------------------------------------------------------------------------------------
EXEC xp_cmdshell 'ECHO 11;She''s Leaving Home;> C:\temp\bulktest.txt' 
EXEC xp_cmdshell 'ECHO 12;Meeting a man from the motor trade;>> C:\temp\bulktest.txt'
go
---------------------------------------------------------------------------------------------------------------
EXECUTE AS LOGIN = 'ERigby'
EXEC FileImportInitiate
go
REVERT
go
---------------------------------------------------------------------------------------------------------------
USE master
go
ALTER DATABASE BulkPlay SET TRUSTWORTHY ON
GRANT AUTHENTICATE SERVER TO BulkPlay$owner
go
EXEC BulkPlay..FileImportInitiate
go
ALTER DATABASE BulkPlay SET TRUSTWORTHY OFF
REVOKE AUTHENTICATE SERVER TO BulkPlay$owner
go
---------------------------------------------------------------------------------------------------------------
USE master 
go
GRANT AUTHENTICATE SERVER TO "SIGN [BulkPlay].[dbo].[reload_table]"
go
EXEC BulkPlay..FileImportInitiate
go
REVOKE AUTHENTICATE SERVER TO "SIGN [BulkPlay].[dbo].[reload_table]"
go
----------------------------------------------------------------------------------------------------------------
USE BulkPlay
go
ALTER PROCEDURE FileImportFromQueue WITH EXECUTE AS OWNER AS
SET XACT_ABORT, NOCOUNT ON
   DECLARE  @dialog  uniqueidentifier,
            @msgtype sysname

   ;RECEIVE TOP(1) @dialog  = conversation_handle,
                   @msgtype = message_type_name
   FROM ReceiverQueue

   IF @msgtype = 'FileImport'
   BEGIN
      BEGIN TRY
         EXEC reload_table
      END TRY
      BEGIN CATCH
         INSERT debug_table(data, token)
            VALUES ('E', substring(error_message(), 1, 128))
      END CATCH
   END
   
   END CONVERSATION @dialog
go
------------------------------------------------------------------------------------------------------------------
/* GrantPermsToSP_server.sql:
  DECLARE @database nvarchar(260) = 'BulkPlay',
           @procname nvarchar(520) = 'FileImportFromQueue'

   DECLARE @perms TABLE (perm nvarchar(400) NOT NULL PRIMARY KEY)
   INSERT @perms VALUES
      ('AUTHENTICATE SERVER')
*/
------------------------------------------------------------------------------------------------------------------
EXEC FileImportInitiate
go
------------------------------------------------------------------------------------------------------------------
CREATE USER sb_user WITHOUT LOGIN
GRANT EXECUTE ON FileImportFromQueue TO sb_user
go
ALTER QUEUE ReceiverQueue
WITH  STATUS = ON, 
      ACTIVATION (STATUS = ON, 
                  PROCEDURE_NAME = dbo.FileImportFromQueue, 
                  MAX_QUEUE_READERS = 1, 
                  EXECUTE AS 'sb_user')
go
-------------------------------------------------------------------------------------------------------------------
ALTER PROCEDURE FileImportFromQueue WITH EXECUTE AS 'sb_user' AS
SET XACT_ABORT, NOCOUNT ON
   DECLARE  @dialog  uniqueidentifier,
            @msgtype sysname

   ;RECEIVE TOP(1) @dialog  = conversation_handle,
                   @msgtype = message_type_name
   FROM ReceiverQueue

   IF @msgtype = 'FileImport'
   BEGIN
      BEGIN TRY
         EXEC reload_table
      END TRY
      BEGIN CATCH
         INSERT debug_table(data, token)
            VALUES ('E', substring(error_message(), 1, 128))
      END CATCH
   END
   
   END CONVERSATION @dialog
go
----------------------------------------------------------------------------------------------------------------------
/* GrantPermsToSP_server.sql
   DECLARE @database nvarchar(260) = 'BulkPlay',
           @procname nvarchar(520) = 'FileImportFromQueue'

   DECLARE @perms TABLE (perm nvarchar(400) NOT NULL PRIMARY KEY)
   INSERT @perms VALUES
      ('AUTHENTICATE SERVER')
*/
----------------------------------------------------------------------------------------------------------------------
EXEC xp_cmdshell 'ECHO 21;Ob-la-di Ob-la-da;> C:\temp\bulktest.txt' 
EXEC xp_cmdshell 'ECHO 22;Life goes on bra;>> C:\temp\bulktest.txt'
go
-----------------------------------------------------------------------------------------------------------------------
EXEC FileImportInitiate
go
-----------------------------------------------------------------------------------------------------------------------
ALTER USER sb_user WITH LOGIN = nosuchlogin
go
-----------------------------------------------------------------------------------------------------------------------
USE master
go
ALTER DATABASE BulkPlay SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DROP DATABASE BulkPlay
go
DROP LOGIN BulkPlay$owner
go
DROP LOGIN ERigby
go
EXEC xp_cmdshell 'DEL C:\temp\bulktest.txt'
go
DROP LOGIN "SIGN [BulkPlay].[dbo].[reload_table]"
go
DROP CERTIFICATE "SIGN [BulkPlay].[dbo].[reload_table]"
go
DROP LOGIN "SIGN [BulkPlay].[dbo].[FileImportFromQueue]"
go
DROP CERTIFICATE "SIGN [BulkPlay].[dbo].[FileImportFromQueue]"