Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1525
  • Last Modified:

Insert permission denied on object "table name", database "Database", owner "dbo" when running stored procedure

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.

Troubleshooting:
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)

Stored Procedure:
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)
AS
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)
GO

Any idea's on fixing this would be appreciated. I do not want to keep the user "AppDBAdmin" in the role of "db_owner".

Thanks, JR

0
MajikTara
Asked:
MajikTara
1 Solution
 
ShogunWadeCommented:
2 points i think here:

1) if a user has permissions to execute a SP they dont need explicit permissions on the underlying objects.  Those permissions are implied be virtue of having the right to execute the stored procedure.

2) if a user has an explicit deny on inserts to a table this will override  point (1).


I suspect its point 2 where you are having a problem.
0
 
MajikTaraAuthor Commented:
ShogunWade:
I agree with #1, I reviewed again the user and it does not have deny on inserts.

The problem I suspect has something to do with the restore, if it was a problem of moving the permissions to the role then I would think it would effect all stored procedures.

I deleted the stored procedure then recreated it, still having the same problem.

Any other idea's?
0
 
ShogunWadeCommented:
hmm.  interesting.    The other stored procs,  they are able to insert into the same table ?
0
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 
MajikTaraAuthor Commented:
No, when I said I was able to run other stored procedures they insert into other tables. If I try a UPDATE to the same table I am also getting the error. I have tried some other stored procedures and it appears it's hit and miss if I can insert or update tables. Some allow inserts/updates some don't. I'm beginning to suspect a problem that may require me to call "the expensive" MS Tech support.

If you have any other idea's let me know. On Monday I have one other thing I can try, if it doesn't work (Hope he's in) then I will call MS. In that case I will award you the points because you supported my thought on permissions.

Keep the idea's coming, it might save me some bucks!
Thanks, MT
0
 
Anthony PerkinsCommented:
ShogunWade

>>1) if a user has permissions to execute a SP they dont need explicit permissions on the underlying objects.  Those permissions are implied be virtue of having the right to execute the stored procedure.<<
And the exception to this would be if the questioner was using dynamic SQL which included an Insert statement.  That does not appear to be the case here, but it might be worth while pointing out to others reading this.
0
 
SimonLarsenCommented:
Just some ideas for you that may be worth looking at before spending real money.

triggers inserting to other tables
sp_change_users_login 'report'
0
 
KeymanCommented:
Usually this type of problem is the result of a break in "chain of ownership"

Could you please check that the "Tables" being using in the sp are owned by dbo.

If they are try to use the Query Tool to change/enter new data into each of the tables in your sproc. For that matter copy the sproc into the query analyzer and remove the ALTER proc code and run it that way and see what you get for an error.


"Chain of ownership" works in that a security check is done on behalf of the user executing the sproc a the sproc level. If all objects being used have the same owner as the sproc security is not checked again. If the owners are different security is checked on those objects and applied at that level.

This is why it can be confusing when you "know" you have permission to the user to run the sproc but don't realize the sproc accessess objects of other owners.

0
 
MajikTaraAuthor Commented:
All -
Well sometimes a weekend can help clear your head. acperkins brought up something about a "dynamic" sql statement. Well I thought about what the process was when the enter key was and found I was looking at the incorrect stored procedure. The stored procedure in question is as follows:

CREATE PROCEDURE [dbo].[sp_jrn_InsertNewRecordToCounter]
@TableName VarChar(25), @ID Int OUTPUT
AS

EXEC ('INSERT INTO '  +  @TableName + '([Date])  VALUES(GetDate())');

SELECT @ID = @@identity
GO

The same question still applies, what permissions issues did I "screw up" for the user to run this? I just moved the permissions from a user to a role. Prior to making this change it ran fine for months!

Sorry about the earlier confusion. MT
0
 
ShogunWadeCommented:
Ok that explains it then.    Just to elaborate on ac's post.    Dynamic SQL runs "under its own context".   thus imagine that you have logged in as the user and are issuing the actual dynamic sql strings.

eg

EXEC ('INSERT MyTable VALUES('1')')

would require the user to have EXPLICIT INSERT permissions on MyTable
0
 
ShogunWadeCommented:
For this reason (amongst others) you should try and avoid using dynamic sql unless it is absolutely necessary.  Because for example as soon as you give the user the ablity to insert into the table he/she can bypass your stored procedures.
0
 
MajikTaraAuthor Commented:
ShogunWade,
So to make sure I fully understand:
1. My vb.net code assigns a user a SQL Username in the connection string of "AppDBAdmin".
2. "AppDBAdmin" is added to the sql role of "DOA App - DBAdmin"
3. I must give role "DOA App - DBAdmin" explicit insert permissions to "MyTable".

Tested - this works. So, it appears that it was a good thing to make the changes in permissions to roles. It would appear that I had explicit rights assigned when they should not have been.

I understand better what the repreccusions of using a dynamic sql statement. I am glad to learn this now, this one statement is the only example of using a dynamic statement and the table contains no "important" data, it's more or less just a counter.

Thanks for the help, MT
0
 
ShogunWadeCommented:
Your welcome.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now