Re: Problem with bulk load security.

(rbielaws@xxxxxxxxx) writes:
I was trying to impersonate a user that worked.
But I tried what you suggested and as expected got a syntax error.

create PROCEDURE Import_File
( @param ...
) with execute as login = 'dom\svc_id'
as
begin ...
.Net SqlClient Data Provider: Msg 102, Level 15, State 1, Procedure
Import_File, Line 10
Incorrect syntax near 'login'.

So I tried this too.

No, I did not suggest that. I suggested that you should try:

   EXECUTE AS LOGIN = '<service account>'
   go
   BULK INSERT ....
   go
   REVERT

There is no CREATE PROCEDURE there. Just a plain EXECUTE AS statement.
The EXECUTE AS *statement* is the only way to impersonate a login.

Try the above from a query window. If this works, review the thread
and look at my earliers posts, how you could use this in a stored
procedure.



--
Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

.

Relevant Pages

  • Re: C# / SQL Related - whats wrong with this Insert Into syntax?
    ... Strange little problem here... ... I'm getting a "Syntax error in Insert Into statement" when it ... tries to execute the SQL. ... execute it using the MS Access query engine, ...
    (microsoft.public.dotnet.languages.csharp)
  • Re: Connecting to Sql Server 2005
    ... When you create the logon (or alter the login) in Enterprise Manager ... allows it the permissions it will need. ... executes the sp's I get the infamous EXECUTE permission denied on object ... I am using sql server authentication - I created a SQL server user in the ...
    (microsoft.public.dotnet.general)
  • Re: Problem with bulk load security.
    ... go back read my previous post about using EXECUTE AS ... you need to impersonate a *server login*. ... Links for SQL Server Books Online: ...
    (microsoft.public.sqlserver.security)
  • Re: SQL Collation
    ... T-SQL I thought this was the best place to post. ... I want to count the rows where the login and password are the same. ... At the moment I get the syntax error Syntax error: ... same syntax as SQL Server, but chances are that it doesn't. ...
    (microsoft.public.sqlserver.programming)
  • Re: SQL Collation
    ... T-SQL I thought this was the best place to post. ... I want to count the rows where the login and password are the same. ... At the moment I get the syntax error Syntax error: ... same syntax as SQL Server, but chances are that it doesn't. ...
    (microsoft.public.sqlserver.programming)