The INSERT permission was denied on the object

The INSERT permission was denied on the object - Printable Version

+- Python Forum (https://python-forum.io)
+-- Forum: Python Coding (https://python-forum.io/forum-7.html)
+--- Forum: General Coding Help (https://python-forum.io/forum-8.html)
+--- Thread: The INSERT permission was denied on the object (/thread-39483.html)

The INSERT permission was denied on the object - Steven5055 - Feb-24-2023

Hi,
I'm trying to use Python to insert data into a SQL Server. pyodbc
I'm basically using python to export data from Server1 & Import into Server 2. with the same database / table names.
I hold the data in a dataframe called DATA
The export works & I can see the rows in my DATA dataframe.
When I try to import the data using the following code to Server 2 I receive an error

Error:
ProgrammingError: ('42000', "[42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]The INSERT permission was denied on the object 'dimEmployee', database 'MIS_DM_Common', schema 'conform'. (229) (SQLExecDirectW)")

My test python INSERT code

for index, row in DATA.iterrows():
     cursor.execute("INSERT INTO MIS_DM_Common.conform.DimEmployee (EmployeeConfKey,SalaryNumberWWWGDWEmployeeKey,WWWEmployeeID,WWWEmployeeNumber,Surname,FirstName,Middlename,PreferredName,EmployeeTitle,CurrentActiveIndicator,InsertEventLogKey,UpdateEventLogKey) values('1','B','C','D','E','F','G','H','I','J','1','1','1')")
connectionB.commit()
cursor.close()

I've checked that I have permission on SQL server.
in Microsoft SQL Server Manager Studio I can execute & it works fine

INSERT INTO MIS_DM_Common.conform.DimEmployee (EmployeeConfKey,SalaryNumber,WWWGDWEmployeeKey,WWWEmployeeID,WWWEmployeeNumber,Surname,FirstName,Middlename,PreferredName,EmployeeTitle,CurrentActiveIndicator,InsertEventLogKey,UpdateEventLogKey)
values('1','B','C','D','E','F','G','H','I','J','1','1','1');

(1 row affected)

Checking with shows that the data has been inserted, so I know I have write permissions.

SELECT * FROM MIS_DM_Common.conform.DimEmployee

I've also ensured that I've ran the following on SQL server, as EmployeeConfKey is a PK that I want to specify.

SET IDENTITY_INSERT MIS_DM_Common.conform.DimEmployee ON

My python connection works as I execute a COUNT query on Server2 before executing the INSERT & everything works.

I've also used this code on a 2nd project (Moving data from Server1 to Server2 (but a different table)) & it has worked successfully. My other project did not have a PK as a sequential number, so I'm assuming it has something to do with the PK.

Thanks in advance.
Steven

RE: The INSERT permission was denied on the object - deanhystad - Feb-25-2023

Do you think you have two database connections st the same time? Can you show that code?

RE: The INSERT permission was denied on the object - Steven5055 - Feb-25-2023

(Feb-25-2023, 08:11 PM)deanhystad Wrote: Do you think you have two database connections st the same time? Can you show that code?

You were correct.
I did not close Server1 correctly & used the same code for Server2.

Thanks for the info..