SQL Server – Permissions Requirement for Bulk Load of Data

Background

There are a copy of options for bulk loading data into SQL Server.

Options

  1. Console
    • BCP.EXE
  2. Transact SQL
    • BULK INSERT

Permissions

SQL Server Permissions

Let us grant the traditional SQL Server permissions such as Insert, Update, Delete, and Select.

Please keep in mind that the Insert permission is sufficient.

 

BCP.EXE

Simple Operation


set _server=DBDR
set _database=DBLAB
set "_user=dbuser"
set "_password=mypassword"

sqlcmd -S %_server% -U %_user% -P %_password% -d %_database% -Q"delete from DBLAB.dbo.FriendAndFamily "

bcp [%_database%].[dbo].[FriendAndFamily] in FriendAndFamily.csv -S %_server%  -U %_user% -P %_password% -c -t ","

Add Identity Value

Arguments

  1. -E
    • Specifies that identity value or values in the data file are to be used for the identity column

 


set _server=DBDR
set _database=DBLAB
set "_user=dbuser"
set "_password=mypassword"

sqlcmd -S %_server% -U %_user% -P %_password% -d %_database% -Q"delete from DBLAB.dbo.FriendAndFamily "

bcp [%_database%].[dbo].[FriendAndFamily] in FriendAndFamily.csv -S %_server%  -U %_user% -P %_password% -c -t "," -E

SQL Server Permissions

  1. Alter Permission
    • To be able use the -E permission, we need to grant the user the alter permission on the targeted SQL table, as well

grant alter on [dbo].[FriendAndFamily] to [dbuser]

If you attempt the -E without you will get the error posted below:

Error

Error – Textual


Starting copy...
SQLState = 37000, NativeError = 1088
Error = [Microsoft][SQL Native Client][SQL Server]Cannot find the object "DBLAB.dbo.FriendAndFamily" because it does not exist or you do not have permissions.

BCP copy in failed

Error – Image

NativeError-1088

When is Alter Permission Needed

  1. Constraints
    • CHECK_CONSTRAINT specified
      • The system does not take the step of disabling constraint
      • And, thus the Alter permission is not needed
    • When CHECK_CONSTRAINT is not specified
      • The system disables all existing enabled constraint
      • Control Permission Needed
      • BTW, this is default
  2. Triggers
    • FIRE_TRIGGER specified
      • Existing Triggers are processed after each upload batch
      • Alter permission not needed
    • When FIRE_TRIGGER not specified
      • The system disables existing INSERT triggers
      • To be able to undertake the process of disabling TRIGGERS, the CONTROL permission is needed
      • BTW, this is default
  3.  Identity
    • KEEPIDENTITY
      • You use the KEEPIDENTITY option to import identity value from data file.

 

BULK INSERT

Simple Operation


 BULK INSERT [dbo].[FriendAndFamily]
 FROM 'd:\temp\FriendAndFamily.csv'
 WITH
 (
     FIELDTERMINATOR =',',
     ROWTERMINATOR = '\n',
 );

Permission

BULK INSERT permissions are granted via:

  1. Server Permissions ( Explicit )
    • GRANT ADMINISTER BULK OPERATIONS
  2. Server Role Permissions ( Implicit )
    • BULKADMIN

use master
go

GRANT ADMINISTER BULK OPERATIONS TO [dbscript];

exec master..sp_addsrvrolemember @loginame = N'dbscript', @rolename = N'bulkadmin'

Review Permissions

Here is stolen code.


declare @serverPermission sysname
declare @securityRole sysname

set @serverPermission = 'ADMINISTER BULK OPERATIONS'
set @securityRole = 'bulkadmin'

;with ServerPermsAndRoles 
as
(
    select
	  [src] = 'Server Permission'
        , principal_name = spr.name
        , principal_type = spr.type_desc
        , security_entity = spm.permission_name collate SQL_Latin1_General_CP1_CI_AS
        , security_type   = 'permission'
        , spm.state_desc
    from sys.server_principals spr
    inner join sys.server_permissions spm
    on spr.principal_id = spm.grantee_principal_id
    where spr.[type] in ('s', 'u')
	and   spm.permission_name collate SQL_Latin1_General_CP1_CI_AS 
			= @serverPermission

    union all

    select
	  [src] = 'Role Membership'
        , principal_name = sp.name
        , principal_type = sp.type_desc
        , security_entity = spr.name
        , security_type   = 'role membership'
        , state_desc      = null

    from sys.server_principals sp

    inner join sys.server_role_members srm

	    on sp.principal_id = srm.member_principal_id

	inner join sys.server_principals spr

		on srm.role_principal_id = spr.principal_id

    where sp.[type] in ('s', 'u')

	and   spr.name = @securityRole

)
select *
from   ServerPermsAndRoles
order by principal_name


 

Summary

To use bcp.exe, an out of process tool, we need to assign Insert permission on the targeted table.

If we will be bringing in Identity values, we need alter permission on the table, as well.

This is because a “SET Identity_Insert” command is issued before and after.

To use “Bulk Insert” command we need server level permissions; specifically “ADMINISTER BULK OPERATIONS”.

The “Bulk Admin” server level role has the “ADMINISTER BULK OPERATIONS” implicitly.

 

References

  1. Bulk Insert ( Transact SQL )
    https://msdn.microsoft.com/en-us/library/ms188365.aspx

 

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s