SQL Server Performance

BULKADMIN and security

Discussion in 'SQL Server 2005 General DBA Questions' started by IDTX2, Dec 3, 2007.

  1. IDTX2 New Member

    We have a SQL instance that includes a consolidation of many different databases. One of our new applications requires BULK INSERT functionality.
    Question #1:
    Am I correct in thinking that the bulkadmin fixed server role is required before a user can execute a bulk insert statement within a database?
    Question #2
    Is bulkadmin STRICTLY for bulk inserts on databases where you already have insert permissions? In other words, if I a user has no permissions in a databases, does the bulkadmin fixed server role grant them any access to the database?
  2. satya Moderator

    With no permission it will fail, within 2005 BULK INSERT Requires INSERT and ADMINISTER BULK OPERATIONS permissions. Additionally, ALTER TABLE permission is required and as per BOL:
    Security Account Delegation (Impersonation)

    If a SQL Server user is logged in using Windows Authentication, the user can read only the files accessible to the user account, independent of the security profile of the SQL Server process.
    When executing the BULK INSERT statement by using sqlcmd or osql, from one computer, inserting data into SQL Server on a second computer, and specifying a data_file on third computer by using a UNC path, you may receive a 4861 error.
    To resolve this error, use SQL Server Authentication and specify a SQL Server login that uses the security profile of the SQL Server process account, or configure Windows to enable security account delegation. For information about how to enable a user account to be trusted for delegation, see Windows Help.
    For more information about this and other security considerations for using BULK INSERT, see /msdn.microsoft.com/mshelp" />Importing Bulk Data by Using BULK INSERT or OPENROWSET(BULK...).
  3. IDTX2 New Member

    For Clarity, ALTER TABLE is only required if any of the following are true:
    Constraints exist and CHECK_CONSTRAINTS is not specified.
    Triggers Exist and FIRE_TRIGGERS is not specified
    You use KEEPIDENTITY to import identity values.
    Thanks for the feedback!!

Share This Page