Re: Problem with bulk load security.

(rbielaws@xxxxxxxxx) writes:
Thanks for pointing me to that 'trustworthy' setting.

Unfortunately it only changed the error I'm getting.

It fixed the error saying I don't have bulk load access but didn't
provide access. It just switched to the error saying the file can't
be opened. On the app server I see the SQL Server trying to get in as
anonymous -- similar to when I use Windows authentication.

So it seems that setting a function to 'execute as' some other user
(even with the trustworthy option on) doesn't give that function
entirely the same abilities as executing the function while actually
logged on as that same user.

It seems our only choice is to hard-code passwords to an SQL Server
account. Very dissappointing.

I think it's too early to give up. Try this:

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

If this works, go back read my previous post about using EXECUTE AS
LOGIN as a statement in the stored procedure and signing it with a
procedure.

It is quite obvious why it did not help to set the database to TRUSTWORTHY:
you are only impersonating a *database user*. To be able to do this,
you need to impersonate a *server login*. I don't know for sure whether
that is a sufficient condition, but it's definitely required.


--
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: Execute Persmission denied on object sp_OACreate
    ... > SQL Server is creating a job behind the scenes. ... > permissions. ... > SA account password and gaining access to the database. ... >>> How can get a user permissions to execute these stored procedures ...
    (microsoft.public.sqlserver.security)
  • RE: exec sp_help_job user account rights
    ... wrapper in which u can call original stored procedure sp_help_job using “WITH ... EXECUTE AS “ clause and then give execute permission of external stored ... Database Administrator, SQL Server 2005 ... the sysadmin fixed role can use sp_help_job to view only the jobs he/she owns. ...
    (microsoft.public.sqlserver.security)
  • Re: SSIS - OLE DB Command - how to retrieve query results ???
    ... my side according to your process, and I managed to execute the task. ... Data Destination: <My SQL Server 2005 Instance>.TestDB ... OLE DB Command: ...
    (microsoft.public.sqlserver.dts)
  • RE: xp_cmdshell, ownership chaining, sql 2000
    ... Cross database ownership chaining enabled ... The procedure is called by a crystal report. ... If I log in to SQL Server through SSMS 2005 using the same user as the ... I get the following error when attempting to execute ...
    (microsoft.public.sqlserver.programming)
  • Re: 3 Simple Security SQL Statements
    ... Kalen Delaney, SQL Server MVP ... window and execute. ... Builds a list of text commands. ... EXEC sp_addrolemember 'WebUsersRole', 'WebUser' ...
    (microsoft.public.sqlserver.security)