Import-SPMetadatawebservicePartitionData and BULK LOAD Problem

Today I was doing some research and troubleshooting issue while using Content Deployment used with Managed Metadata Service(MMS). I was moving MMS Term Store from Author to Production Farm using the Windows PowerShell command Import-SPMetadatawebservicePartitionData. This commands takes the export file generated by using Import-SPMetadatawebservicePartitionData and imports the term sets in the target MMS.

The commands failed with the following error message “You do not have permission to use the bulk load statement”. This was kind of surprising as I was logged in as box admin who is SQL admin also. I was having the appropriate permissions and it should have just worked. There are few guys who used it and it just worked fine for them. The only difference in my environment is that I run my services with separate accounts and I try to mimic things as close to production with respect to security. I checked the error message and cause seem to be somewhere in SQL perms. I checked the permissions of my MMS service account and it was not having the SQL server BULK ADMIN role assigned to it. When I added my service account to this server role, things started working fine. Looks like the command uses bulk insert SQL command to import the terms in SQL DB. Since the service account was not having these permissions, it failed. I checked with a dev and this seems to be a known issue. 

So the workaround is to add service account to BULK ADMIN role when you want to import the term store and remove it after operation is complete.