sql-serverbulkopenrowset

SQL Server bulk openrowset access is denied for windows user but not for sa

When I am logged in as the sa user I am able to run the following command:

SELECT * 
FROM OPENROWSET(BULK '\\server1\files\test.pdf', SINGLE_BLOB) x

However, when I log in as my windows user who has sysadmin, bulkadmin and serveradmin roles I get the following error:

Msg 4861, Level 16, State 1, Line 1
Cannot bulk load because the file "\server1\files\test.pdf" could not be opened. Operating system error code 5(Access is denied.).

What permissions am I missing from my Windows user?

Solution

  • I was able to circumvent this problem by creating a SQL job that called my stored procedure where the bulk insert script occurs. When the job is run, the SQL agent credentials are used and the blob is read.