Post reply

Problem with 'execute as login' on running stored procedure

  • March 14, 2016 at 7:08 pm

    #314105

    Hi Everyone.

    We have a sp1 which is being called by User1. In this SP there are some tables that User1 does not have access to them, so I have to create another user2 and give proper permission to that user.

    when I ran the sp1 while I am login by User1 with 'execute as login = 'User2'',I recieved below error message:

    Cannot execute as the server principal because the principal "User2" does not exist, this type of principal cannot be impersonated, or you do not have permission.

    But if I ran the same statment while I login as sysadmin , I dont have any problem.

    I would appreciate if someone help me on this.

    Thanks

  • March 15, 2016 at 9:37 am

    #1865297

    User1 must be given permission to impersonate user2, but I'd like to understand why ownership chaining[/url] isn't sufficient in this case, so please answer the following questions:

    1. Are all the objects in the same database?

    2. What schemas are the objects in?

    3. Who owns the schemas?

    SELECT

    S.name AS SchemaName,

    DP.name AS OwnerName

    FROM

    sys.schemas AS S

    JOIN sys.database_principals AS DP

    ON S.principal_id = DP.principal_id;

    If the schemas have the same owner ownership chaining should work.

    Jack Corbett
    Consultant - Straight Path Solutions
    Check out these links on how to get faster and more accurate answers:
    Forum Etiquette: How to post data/code on a forum to get the best help
    Need an Answer? Actually, No ... You Need a Question

  • March 15, 2016 at 10:25 am

    #1865334

    Please don't create multiple posts for the same problem.

    Answer here

Viewing 3 posts - 1 through 2 (of 2 total)

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