service principals authentication to SQL DB

Question

Hi

I have a pipeline setup from ADSL to SQL which would truncate and insert data.

When I am giving truncate table in Pre-copy Script, it fails with permission error.

How permissions needs to be given in SQL DB to enable truncate table option. I have service principal ID in Linked Services and I need steps to be performed in SQL DB

Answer 1

Hey,

CREATE USER [<<SP name>>] FROM EXTERNAL PROVIDER   
  
exec sp_addrolemember 'db_owner', '<<SPName>>'  
   
  
if min privelage:  
  
exec sp_addrolemember 'db_ddladmin', '<<SPName>>'  
  

Since the test connection in linked service is success and the job is failing only for truncate, so you need to execute either the ddladmin aspect for min privelage aspect or the db_owner with max privelage