Home Forums SQL Server 2014 Administration - SQL Server 2014 You do not have permission to use the bulk load statement. RE: You do not have permission to use the bulk load statement.

  • January 17, 2017 at 7:25 pm

    #1923313

    Steve Jones - SSC Editor - Monday, January 16, 2017 11:49 AM

    In playing more, I found this article: https://support.microsoft.com/en-us/kb/913422
    This article notes: For security considerations, the server-scoped permissions are stripped down when you impersonate a database user unless the system administrator has explicitly set SQL Server to trust the impersonated context at the server-scope. In this case, a login with the control server server-scoped permission has no permissions to access any particular database. Therefore, the trigger module that is executed as this login cannot run.

    I enabled trustworthy and I was able to run the command with the EXECUTE AS SELF setting.

    OK, on 5th or 6th pass through this it started to make sense.
    Server login mapped to a db user creates a procedure AS SELF, and on this stage the db user which is the author of the proc gets "disconnected", or "orphaned" from from Server login.
    Then when procedure is executed it runs under the context of that db user which not necessarily the same user which is currently linked to the current Server login with the same name.
    Server needs to "trust" that database to allow its users (which created the procedures back then) perform server-scoped operations.

    Such "disconnection" does not happen when executed AS CALLER, obviously.

    I made the database TRUSTWORTHY and it indeed fixed the issue.
    Thanks.

    _____________
    Code for TallyGenerator