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

Posted on 2004-11-05
Last Modified: 2008-01-09
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)

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)
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".

Thanks, JR

Question by:MajikTara
    LVL 18

    Expert Comment

    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.

    Author Comment

    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?
    LVL 18

    Expert Comment

    hmm.  interesting.    The other stored procs,  they are able to insert into the same table ?

    Author Comment

    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
    LVL 75

    Expert Comment

    by:Anthony Perkins

    >>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.
    LVL 8

    Expert Comment

    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'

    Expert Comment

    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.


    Author Comment

    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

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

    SELECT @ID = @@identity

    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
    LVL 18

    Accepted Solution

    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.


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

    would require the user to have EXPLICIT INSERT permissions on MyTable
    LVL 18

    Expert Comment

    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.

    Author Comment

    So to make sure I fully understand:
    1. My 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
    LVL 18

    Expert Comment

    Your welcome.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Free Trending Threat Insights Every Day

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
    JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
    Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
    Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now