Post reply

Cannot execute as the database principal

  • November 14, 2013 at 10:39 pm

    #285178

    I have been handed a task. I don't have a lot of experiance in SQL. I know this probably isn't the best approach. But, it is what I have been asked to make work (I can't sign the code or use asymetric keys). When I execute a stored procedure I get the following error. I think it is related to double impersonation with local SQL accounts.

    Msg 15517 - Cannot execute as the database principal because the principal "user1" does not exist, this type of principal cannot be impersonated, or you do not have permission.

    We have two diffrent instaces of MSSQL. Each instance has a database. In Instance 1 we have Database A. In Instance 2 we have Database B. We have a linked server in Instance 1 that connects to Database B. In Database A we have a stored procedure that retrieves data using the linked server from Database B. This stored procedure uses Execute As User with the impersonated account User1 and works when I run it from the console. However, when it is run in a script or from the console it fails. The stored procedure is being called with the Execute As User with the account user5. How can I get the double impersonation to work? For example:

    We are calling the stored procedure this:

    use 'Database A'

    go

    execute as user = 'user5'

    go

    schema-1.StoredProcedure_GetData

    The stored procedure is executing a select statement with the Execute As User = 'user1'

    Script 1

    EXECUTE AS USER = 'user5'

    (run against DB-A)

    (Runs Stored Proc-1 on DB-A)

    (Fails Here??)

    |

    |

    MSSQL Instance-1

    |

    |

    MSQL DB-A

    |

    |

    |

    Stored Proc-1

    EXECUTE AS USER = 'user1'

    (Gets data from DB-B)

    (Using Linked Server)

    (Execute AS USER seems to work)

    ||

    ||

    MSQL Linked SVR 1

    ||

    ||

    MSSQL Instance-2

    |

    |

    |

    MSQL DB-B

  • November 15, 2013 at 8:55 am

    #1666762

    I was able to get it working after going through the EXECUTE AS (Transact-SQL) article with fresh eyes. I didn't set the impersonate permission on the user account. To set it I used the following

    USE DB-A;

    GRANT IMPERSONATE ON USER::user1 TO user5;

    GO

    My issue is resolved. Thank you.

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

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