We help IT Professionals succeed at work.

SQL insert permission denied even after granting insert permission.

I have a stored procedure in one database that writes to another database. Security is assigned using SQL Authentication.
For each user I have issued
GRANT DELETE,UPDATE,INSERT TO JOBCOSTDETAIL TO USERNAME

This stored procedure is called via a  VS C# program. Whenever a user clicks the button to run the stored procedure they get:
The INSERT permission was denied on the object 'JOBCOSTDETAIL' ,database 'DATABASE',schema 'dbo'

I could change my code to run the sp as 'sa' but I should not need to do that. When I check the properties of this user on this database they do have INSERT permission as dbo. What else do I need to set to get an average user the ability to write to this table in this other database.
Comment
Watch Question

CERTIFIED EXPERT
Distinguished Expert 2019

Commented:
are you certain the username is seen as the one logged in? Check the process, use profiler to capture and see whether the connection is seen from the username or another.

https://docs.microsoft.com/en-us/sql/relational-databases/stored-procedures/grant-permissions-on-a-stored-procedure?view=sql-server-2017

You overlooked something.
What is the name of the database? Which table are you applying the rights to?
ste5anSenior Developer
CERTIFIED EXPERT

Commented:
When you say "other database", what cross-database permission concept do you use? Permission and owner ship chaining?
rwheeler23President

Author

Commented:
These two databases are under the same SQL instance. I have the user name and password and I am logging into my application as her. The table name is JOBCOSTDETAIL and the database name is MDGPWORK. I will try running SQL Profiler to see if it shows me anything.
CERTIFIED EXPERT
Distinguished Expert 2019

Commented:
You need to grant database.tablename
Add select rights. Use ssms to login as the user and confirm you can query the table.
rwheeler23President

Author

Commented:
Could it be this? The ERP solution that calls my program encrypts the user's password. So if I try to log into SSMS using their name and password it will not log them in. I get the users credentials from the ERP solution. All is fine as long as the user does things inside the ERP solution. If this is it I will need to change my connection to use 'sa' or I can create an account just for my program.
CERTIFIED EXPERT
Distinguished Expert 2019

Commented:
An erp solution, can use a central SQL login while limiting the user based on a mask, I do not think an erp will be creating individual user SQL logins, check the security portion of the database server via ssms.

See whether the erp application provides a facility to do what you are after.
Often, you have to fully understand the db schema, and data structure to avoid breaking data integrity.
You can not/shoukd not rely on db architects to gave designed and include relevant constraints, triggers.....to prevent data corruption, orphaned records.
rwheeler23President

Author

Commented:
This ERP creates the SQL logins and passes the names down to SQL. User passwords as encrypted. All users go into a SQL group and rights are granted to the group. Each database that belongs to the ERP solution has the same list of users. I have created the same users in the external database. This external database is the home to temporary tables. If I need to I can move them into the of the ERP databases. The schema on all databases is dbo.
CERTIFIED EXPERT
Distinguished Expert 2019

Commented:
Dbo is database owner.
The structure of the db is what I am talking about.

Check whether the erp includes an API through which you can achieve your goals.
rwheeler23President

Author

Commented:
The database in question is unrelated to the ERP solution so there is no API. The API is used for ERP related databases. There are no issues writing to any of the ERP related databases. It is only this unrelated database that has the permission issue. If I cannot crack this I may bring the tables into the ERP database.
rwheeler23President

Author

Commented:
I finally found what was causing this. I created a new user using the ERP solution and assigned the same rights as the user having this issue. Much to my surprise the problem did not occur for this user. I then went SQL property by SQL property comparing the settings. What I discovered was when I got to Schemas somehow this user was the owner of the schema called ERPGRP. This is the group that the ERP solution creates and all rights are assign to this group. In all the ERP databases the owner of the ERPGRP schema was the group called ERPGRP not a user. Once I switched it to be like the ERP databases the stored procedure could be run by a normal ERP solution user. I have no idea how this user got to be assigned as the owner of this schema. Why would this result in INSERT permission denied?
President
Commented:
This issue was caused by the way Microsoft designed this ERP solution. The tips provided here help guide me to finding the solution.