Synapse database permissions issue

Pavankumar-3526 306

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. I am getting this error.

User's image

ShaikMaheer-MSFT 37,971 Reputation points Microsoft Employee

2023-02-06T11:34:40.09+00:00

Hi anonymous,

Thank you for posting query in Microsoft Q&A Platform.

Hope you are using same login and user details in linked service also. Could you please share your linked service configuration details and also the login user privileges details which you assigned along with scripts? That way i can try to repro issue at may end by granting exact same roles and privileges. Thank you.
Pavankumar-3526 306 Reputation points

2023-02-06T21:43:35.5633333+00:00

Hi Shaik,

Yes, I am using the same SQL login. It seems like a bug with synapse. I can able to test the linked service.

Here is the script I am using.

on master

CREATE LOGIN [test] WITH PASSWORD=N'Password@1'

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.

Appreciate your help!
Pavankumar-3526 306 Reputation points

2023-02-08T23:11:10.0833333+00:00

Hi Shaik,

Were you able to reproduce the issue? Appreciate your help!