When attempting to run a stored procedure from my VB.Net app I am receiving the following error:
Insert permission denied on object "table name", database "Database", owner "dbo" when running stored procedure.
This stored procedure worked fine prior to today. Other stored procedures that also insert into tables in the same database are working fine.
Changes made today:
1. Restored a backup of my database with a new name "DeptOfAuditTest".
2. Created a new role in this database "DOA App - DBAdmin"
3. Removed permissions from user "AppDBAdmin" for running stored procedures.
4. Made user "AppDBAdmin" part of role "DOA App - DBAdmin".
5. Gave role "DOA App - DBAdmin" permission to execute stored procedures.
1. If I add user to role "db_owner" then stored procedure runs fine.
2. The owner of the stored procedure is dbo as is the table in question.
3. I have reviewed permissions and they appear to be ok. (confirmed against stored procedures and tables that are working fine)
CREATE PROCEDURE [dbo].[sp_jrn_InsertProjectTrainingDivision] (@ProjectNumber Varchar(8), @ProjectDescription Varchar(200), @Active Bit,
@Approved Bit, @Cancelled Bit, @DivisionID Integer, @SubDivisionID Integer, @EmployeeID Integer, @WolfsReq Bit,
@DeptStrategyID Integer, @ProjectDetailID Integer, @AuditFromDate DateTime, @AuditToDate DateTime, @TDivisionID Integer, @EntityID Integer)
INSERT INTO tblProject(ProjectNumber, ProjectDescription, Active_Project, Approved, Cancelled, DivisionID,
SubDivisionID, EmployeeID, WolfsReq, DeptStrategyID, ProjectDetailID,
AuditFromDate, AuditToDate, TrainingDivisionID, CustomerID)
VALUES(@ProjectNumber, @ProjectDescription, @Active, @Approved, @Cancelled, @DivisionID, @SubDivisionID, @EmployeeID,
@WolfsReq, @DeptStrategyID, @ProjectDetailID, @AuditFromDate, @AuditToDate, @TDivisionID, @EntityID)
Any idea's on fixing this would be appreciated. I do not want to keep the user "AppDBAdmin" in the role of "db_owner".