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]"