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

MajikTaraAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.