Help with OPENROWSET in SQL Server (Impersonation issue)

Basically I am looking to select the contents of a pdf into a table.

I am using this query:

SELECT *
FROM   OPENROWSET(BULK N'\\Server\Share\filename.pdf', SINGLE_BLOB) rs

The query won't run because my username doesn't have per开发者_开发技巧missions on that server (nor should it), but I need to be able to authenticate as a different user to execute this query.

It probably won't be an issue in production as I believe the account running the command will have the proper permissions, but as it is, I would like to be able to "impersonate" this user (I obviously have its credentials) and run the query from my account. Any ideas on how to do this?

Thanks...

You can specify your connection string details in OPENROWSET. See the { 'datasource';'user_id';'password' | 'provider_string' } portion of the syntax below.

OPENROWSET 
( { 'provider_name', { 'datasource';'user_id';'password' 
   | 'provider_string' } 
   , {   [ catalog. ] [ schema. ] object 
       | 'query' 
     } 
   | BULK 'data_file', 
       { FORMATFILE ='format_file_path' [ <bulk_options> ]
       | SINGLE_BLOB | SINGLE_CLOB | SINGLE_NCLOB }
} )<bulk_options> ::=
   [ , CODEPAGE = { 'ACP' | 'OEM' | 'RAW' | 'code_page' } ] 
   [ , ERRORFILE ='file_name' ]
   [ , FIRSTROW = first_row ] 
   [ , LASTROW = last_row ] 
   [ , MAXERRORS = maximum_errors ] 
   [ , ROWS_PER_BATCH =rows_per_batch ] 

Wrap it in a stored procedure and use EXECUTE AS. You'll have to impersonate someone with server rights as well as SQL rights.

http://msdn.microsoft.com/en-us/library/ms188354.aspx

If you'll be doing it a lot, you can do it as an OSQL command line query (through a batch file or similar):

OSQL -U [username] -p [password] -S [server] -D [database] -q "EXIT(SELECT * FROM OPENROWSET(BULK N'\\Server\Share\filename.pdf', SINGLE_BLOB) rs)"

You need to determine if you want the credentials stored in the database or in a file you can delete once you get to production.