I was able to change where the error occured. I've included my test script
for reference.
Before, when I created the database users, I didn't specify FOR LOGIN, and
in that case, as the machine account, I got my Msg 33009 on the statement
EXECUTE AS LOGIN = 'BulkUser';
In this version, I did specify FOR LOGIN, and now it did the above statement
OK, but now I got the same message when I tried to EXEC the procedure.
I've checked the machine account currently has every system role. I still
don't understand how it works on my account and not the machine account. I'm
hoping the fact I moved the error will be a clue as what I should do.
Bob
--!00_TestBulkScript.SQL
USE master;
SET NOCOUNT ON;
GO
-- Drop existing database and login accounts
IF EXISTS(SELECT * FROM sys.databases WHERE name = 'BulkCopyTest')
DROP DATABASE BulkCopyTest;
IF EXISTS(SELECT * FROM sys.server_principals WHERE name = 'BulkUser')
DROP LOGIN BulkUser;
IF EXISTS(SELECT * FROM sys.server_principals WHERE name =
'BulkAccount')DROP LOGIN BulkAccount;
GO
CREATE LOGIN BulkUser WITH PASSWORD = 'bulk$1user';
CREATE LOGIN BulkAccount WITH PASSWORD = 'bulk$1account';
GRANT ADMINISTER BULK OPERATIONS TO BulkAccount;
--REVOKE CONNECT SQL FROM BulkAccount;
GO
CREATE DATABASE BulkCopyTest;
ALTER DATABASE BulkCopyTest SET TRUSTWORTHY ON;
GO
USE BulkCopyTest;
IF NOT EXISTS(SELECT * FROM sys.database_principals WHERE name =
'BulkUser')CREATE USER BulkUser FOR LOGIN BulkUser;
IF NOT EXISTS(SELECT * FROM sys.database_principals WHERE name =
'BulkAccount')
CREATE USER BulkAccount FOR LOGIN BulkAccount;
GO
IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME =
'vContext')
DROP VIEW dbo.vContext;
GO
CREATE VIEW dbo.vContext AS
SELECT
GETDATE() AS CurrentTime
,ORIGINAL_LOGIN() AS OriginalLogin
,SUSER_SNAME() AS SUserSName
,SYSTEM_USER AS SystemUser
,USER_NAME() AS UserName
,SESSION_USER AS SessionUser
,CURRENT_USER AS CurrentUser;
GO
GRANT SELECT ON dbo.vContext TO public;
GO
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE =
'BASE TABLE' AND TABLE_NAME = 'BulkTable')
BEGIN
CREATE TABLE dbo.BulkTable
(RowNum INT IDENTITY(1,1) PRIMARY KEY
,DT DATETIME DEFAULT (GETDATE())
,H1 VARCHAR(36)
,H2 VARCHAR(36)
);
GRANT INSERT ON dbo.BulkTable TO BulkAccount;
--Not sure I need this, considering ownership chaining.
END;
GO
IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME =
'vBulkTable')
DROP VIEW dbo.vBulkTable;
GO
CREATE VIEW dbo.vBulkTable AS SELECT H1,H2 FROM dbo.BulkTable;
GO
GRANT INSERT ON dbo.vBulkTable TO BulkAccount;
GO
IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_TYPE =
'PROCEDURE' AND ROUTINE_NAME = 'BulkInsertTest')
DROP PROCEDURE dbo.BulkInsertTest;
GO
CREATE PROCEDURE dbo.BulkInsertTest
(@UNCFilePath AS VARCHAR(256)
,@FirstRow AS INT = 2
,@ErrorString VARCHAR(4000) = '' OUTPUT
) WITH EXECUTE AS 'BulkAccount'
AS
DECLARE @SQL VARCHAR(4000);
BEGIN
SET @SQL = 'BULK INSERT dbo.vBulkTable FROM ';
SET @SQL = @SQL + CHAR(39) + @UNCFilePath + CHAR(39);
SET @SQL = @SQL + ' WITH (TABLOCK,FirstRow=' + CAST(@FirstRow AS VARCHAR) +
')';
SELECT *,@SQL AS SQLStatement FROM dbo.vContext;
EXEC(@SQL);
END;
GO
GRANT EXECUTE ON dbo.BulkInsertTest TO public;
GO
EXECUTE AS LOGIN = 'BulkUser';
SELECT * FROM dbo.vContext;
DECLARE @strError AS VARCHAR(4000);
EXEC dbo.BulkInsertTest
@UNCFilePath = 'H:\SQLServerBulkInsertTest\ServiceBrokerBulkFile.txt'
,@FirstRow = 2
,@ErrorString = @strError OUTPUT;
IF LEN(ISNULL(@strError,'')) > 0 SELECT @strError AS ErrorString;
REVERT;
SELECT * FROM dbo.vContext;
SELECT * FROM dbo.BulkTable;
GO
-- Clean Up
USE master;
IF EXISTS(SELECT * FROM sys.databases WHERE name = 'BulkCopyTest')
DROP DATABASE BulkCopyTest;
IF EXISTS(SELECT * FROM sys.server_principals WHERE name = 'BulkUser')
DROP LOGIN BulkUser;
IF EXISTS(SELECT * FROM sys.server_principals WHERE name =
'BulkAccount')DROP LOGIN BulkAccount;
GO
>> Stay informed about: Permissions and Bulk Insert 
FAQ
Search
Profile
Private Messages
Log in
