Synapse Dedicated SQL pool issue

Question

I created a SQL user and granted control privileges to the user and was able to perform the DML operations via SSMS.

but when I use the linked service with that user, I am not able to perform any DML operations via dataflow. I am getting this error.

Here is the script I am using.

on master

CREATE LOGIN [test] WITH PASSWORD=N'xxxxxx'

CREATE USER test FROM LOGIN test

on testDB

CREATE USER test FROM LOGIN test

on testDB

Grant control on schema::dbo to test;

Synapse error from the pipeline when I tried to perform the DML operations.

Operation on target Data flow1 failed: {"StatusCode":"DFExecutorUserError","Message":"Job failed due to reason: at Sink 'sink1': User does not have permission to perform this action.\r\nChanged database context to 'testdb'.","Details":"shaded.msdataflow.com.microsoft.sqlserver.jdbc.SQLServerException: User does not have permission to perform this action.\r\nChanged database context to 'testdb'.\n\tat shaded.msdataflow.com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:265)\n\tat

but I can able to execute DML operations from SSMS or datastudio with the same sql login.

The issue is happening only when I use the synapse or adf pipelines.

Answer 1

Hello @Pavankumar-3526 :

I think I have a repro and this only happens when you have AutoCreate table option is set in the copy activity.

Error

ErrorCode=SqlOperationFailed,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=A database operation failed with the following error: 'User does not have permission to perform this action. Changed database context to 'synapse'.',Source=,''Type=System.Data.SqlClient.SqlException,Message=User does not have permission to perform this action. Changed database context to 'DBAName'.,Source=.Net SqlClient Data Provider,SqlErrorNumber=6004,Class=14,ErrorCode=-2146232060,State=9,Errors=[{Class=14,Number=6004,State=9,Message=User does not have permission to perform this action. Changed database context to 'synapse'.,},],'

Solution is to provide the User (in your case test) the required permission.

GRANT CREATE TABLE TO test

Let me know how its goes

Thanks

Himanshu
Please accept the answer if you think it helped you .

Answer 2

Hi

Thanks for reaching out to Microsoft Q&A.

Clearly looks like a permission issue reading at the error message. What role does the login has in the database? that you are using in the linked service?

Please Upvote and Accept as answer if the reply was helpful, this will be helpful to other community members.