Post reply

Object permissions

  • December 28, 2009 at 4:41 am

    #217858

    Hi,

    I created a table as a "db_owner".

    create table sample (id int);

    Then granted only SELECT privilege to the user "u1"

    Also, i have created a stored procedure which will do an INSERT operation on table "sample" and granted EXECUTE permission to user "u1"

    create proc sp1

    as

    begin

    insert into sample

    select 1

    end;

    The observation is, if i directly do the INSERT operation on sample, it is throwing me an error saying "insufficent privs" but if i execute the stored procedure which is having the INSERT INTO SAMPLE statement, i being executed.

    It is not throwing me an error saying so and so user has only SELECT but not INSERT permission. Instead, the INSERT is being

    successful.

    Can anybody explain why is it so?

    What all other operations i.e DELETE, UPDATE is possible if i embed them in a stored procedure and GRANT EXECUTE permission on stored procedure?

    Thanks in advance.

  • December 28, 2009 at 5:16 am

    #1096640

    Check EXECUTE AS Clause in BOL

  • December 28, 2009 at 6:39 am

    #1096653

    everything is working as expected; the user cannot directly insert/update/delete from the tables.

    the assumption is that if you grant EXECUTE to a stored procedure, then the procedure can do whatever it is going to do, including update/insert/delete.

    this is by design; it is very very common that an end user has no permissions whatsoever to the base tables, and only EXECUTE permissions to a suite of procedures that would do the SELECT or INSERT or UPDATE.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • December 28, 2009 at 6:50 am

    #1096658

    Hi vyas,

    Correct me if i am wrong!

    what does EXECUTE AS clause has to play a role here?

    do you think, the creator's permissions are being impersonated when the user having "EXECUTE" privilege is calling the stored procedure?

    Please confirm or any other reasons????

  • December 28, 2009 at 9:35 pm

    #1096891

    Yes,Creator's permissions are being impersonated when the user having "EXECUTE" privilege is calling the stored procedure.

  • December 28, 2009 at 10:32 pm

    #1096901

    Execute AS "User1" impersonates the permissions of the user specified , in this case User1 and not the created user.

    "Keep Trying"

  • December 29, 2009 at 12:27 am

    #1096923

    Thanks All.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply