User Account receives primary key violation but not another user

Mia 1 Reputation point
2020-08-28T00:42:41.373+00:00

Hi

We're trying to set up a proxy user to run an SQL Server job, but there are steps in the SSIS package with are MERGE statements.

The user account set up, which has sysadmin rights against the databases, receives a 'Violation of PRIMARY KEY constraint' error when doing this statement. Another user, with the exact same security permissions, does not get this violation, and not does the SQL Server Agent.

When separating the statement out to its separate parts, inserting in TEMPDB, and then doing a MERGE, no such violation occurs.

I have been unable to find any reference to this problem, and any assistance would be greatly appreciated. We're using SQL Server 2016.

Thanks.

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,486 questions
SQL Server Integration Services
SQL Server Integration Services
A Microsoft platform for building enterprise-level data integration and data transformations solutions.
2,438 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,536 questions
0 comments No comments
{count} votes

2 answers

Sort by: Most helpful
  1. Monalv-MSFT 5,891 Reputation points
    2020-08-28T09:06:55.873+00:00

    Hi Mia,

    May I know if you insert the duplicated value into the table with Primary Key?

    Hope the following links will be helpful:

    [Dealing with Violation of PRIMARY KEY constraint SSIS closed

    I am using ssis and I am getting this error "Violation of PRIMARY KEY constraint .Cannot insert duplicate key in object . The duplicate key value is

    Best Regards,
    Mona


    If the response is helpful, please click "Accept Answer" and upvote it.


  2. Tom Phillips 17,716 Reputation points
    2020-08-28T17:15:27.937+00:00

    PK violations are not related to security in any way. You must have a logic or data issue.

    0 comments No comments