Permission - Execute as - Issue

  • Hi,

    Having some trouble seeing where the issue is here: 

    alter PROCEDURE pec.jpwPermissionTest1_20190128
    with
    EXECUTE AS owner
    AS
    BEGIN
    -- this one fails because it is set to execute as owner, which becomes the owner of the DBO schema, which is SA

    SELECT * FROM pecmaster.dbo.tGeoCountyEnum

    END

    go

    alter PROCEDURE pec.jpwPermissionTest2_20190128
    with
    EXECUTE AS 'PREMIER\reportaccountrunner'
    AS
    BEGIN
    -- even though reports is set up as a DB OWNER user on pecMaster, this fails (probably) because i'm emulating the *evips_prod* reports user, which has no cross-database trust to pecMaster
    SELECT * FROM pecmaster.dbo.tGeoCountyEnum

    END

    GO

    alter PROCEDURE pec.jpwPermissionTest3_20190128
    with
    EXECUTE AS 'PREMIER\jophsmith'
    AS
    BEGIN
    -- even a super user account in the same example as #2 does not work, since its the cross-database trust that is the issue
    SELECT * FROM pecmaster.dbo.tGeoCountyEnum

    END

  • krypto69 - Monday, January 28, 2019 1:43 PM

    Hi,

    Having some trouble seeing where the issue is here: 

    alter PROCEDURE pec.jpwPermissionTest1_20190128
    with
    EXECUTE AS owner
    AS
    BEGIN
    -- this one fails because it is set to execute as owner, which becomes the owner of the DBO schema, which is SA

    SELECT * FROM pecmaster.dbo.tGeoCountyEnum

    END

    go

    alter PROCEDURE pec.jpwPermissionTest2_20190128
    with
    EXECUTE AS 'PREMIER\reportaccountrunner'
    AS
    BEGIN
    -- even though reports is set up as a DB OWNER user on pecMaster, this fails (probably) because i'm emulating the *evips_prod* reports user, which has no cross-database trust to pecMaster
    SELECT * FROM pecmaster.dbo.tGeoCountyEnum

    END

    GO

    alter PROCEDURE pec.jpwPermissionTest3_20190128
    with
    EXECUTE AS 'PREMIER\jophsmith'
    AS
    BEGIN
    -- even a super user account in the same example as #2 does not work, since its the cross-database trust that is the issue
    SELECT * FROM pecmaster.dbo.tGeoCountyEnum

    END

    With the cross database issues, you need to open things up more unless you go the more secure route of signing the stored procedure. I'd go that route:
    Tutorial: Signing Stored Procedures with a Certificate

    For a very thorough read on the subject you are dealing with, I think this is the one of the best articles on the subject:
    Packaging Permissions in Stored Procedures

    Sue

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

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