CODEWITHSUNDEEP
sql-serverbulkopenrowset
warrenk
warrenk

Reputation: 111

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?

Upvotes: 0

Views: 1249

Answers (2)

warrenk
warrenk

Reputation: 111

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.

Upvotes: 0

David Browne - Microsoft
David Browne - Microsoft

Reputation: 89371

sa will run OPENROWSET(BULK as the SQL Server Service Account, which will map to the Machine Account for built-in users like NT SERVICE\MSSQLSERVER or Network Service. Windows Logins will run OPENROWSET(BULK as themselves.

Upvotes: 0

Related Questions