Re: Problem with bulk load security.

On Jan 29, 5:14 pm, Erland Sommarskog <esq...@xxxxxxxxxxxxx> wrote:
 (rbiel...@xxxxxxxxx) writes:
I guess I'm confused.
The only user that didn't have the bulkadmin roll was the ID that SQL
Server itself runs under.
That one wasn't even in the ACL anywhere.
So I assumed that was the 'service account' you were talking about.

But after adding the login and giving it bulkadmin nothing changed.
I still get "You do not have permission to use the bulk load
statement."
On one level I thought it made sense but since it didn't work I'm not
completely sure I understood what you wanted me to try.

You said that you did EXECUTE AS. Did you to EXECUTE AS USER or EXECUTE
AS LOGIN? You need to do the latter, or else you will be sandboxed into
the current database and can't do things outside it, unless the database
is marked as trustworthy.

--
Erland Sommarskog, SQL Server MVP, esq...@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


I think that describes what I did.
I logged in with the ID that worked.
Then I defined the procedure using 'with execute as self'.
Which should be the same as 'with execute as <working ID>'
It's the only ID that seemed to have any hope of working.

As for user vs login, I don't see a syntax difference.
I only see this which indicates 'user_name' is my only option.

DDL Triggers with Server Scope and logon triggers
{ EXEC | EXECUTE } AS { CALLER | SELF | 'login_name' }

' login_name '
Specifies the statements inside the module execute in the context of
the SQL Server login specified in login_name. Permissions for any
objects within the module are verified against login_name. login_name
can be specified only for DDL triggers with server scope or logon
triggers.

Functions (except inline table-valued functions), Stored Procedures,
and DML Triggers
{ EXEC | EXECUTE } AS { CALLER | SELF | OWNER | 'user_name' }

' user_name '
Specifies the statements inside the module execute in the context of
the user specified in user_name. Permissions for any objects within
the module are verified against user_name. user_name cannot be
specified for DDL triggers with server scope or logon triggers. Use
login_name instead.


.

Relevant Pages

  • Re: Stored procedure/trigger and scripts
    ... SQL Server has permissions to execute xp_cmdshell. ... Please read up on xp_cmdshell within Books Online (within the SQL Server program group). ... >> client to change their password they have to call the "Client Relations" ...
    (microsoft.public.sqlserver.programming)
  • Re: DTS package not executing from within a webservice
    ... method on the Server A which has a web method, Am trying to execute a DTS ... created a wrapper and using the methods of the DTS.dll ... Not associated with a trusted SQL ... Server connection. ...
    (microsoft.public.dotnet.framework.aspnet.webservices)
  • Re: How to monitor Triggers in VB6
    ... You can use Profiler to or SQL Trace to monitor what's currently executing ... Vyas, MVP (SQL Server) ... > I have this triggers in T-SQL to execute when a product qty is 0, ...
    (microsoft.public.sqlserver.programming)
  • Re: effect of trigger in Access?
    ... I'm thinking I should be poking around in a MySQL or SQL ... Server area. ... > table level events (triggers). ... > and also have retained table level events for more ...
    (microsoft.public.access.externaldata)
  • Re: User and Login auditing
    ... create triggers on system tables and then you will find out ... and hope that your employer doesn't mind you putting the SQL ... Server box in this position. ... You could put triggers on your trace table. ...
    (microsoft.public.sqlserver.security)