Post reply

EXECUTE AS impersonation

  • September 11, 2014 at 3:23 pm

    #293167

    I am still a little unsure about the implications of using EXECUTE AS. I was starting to edit the job steps to be Stored Procedures that run as a domain account with minimal rights and they run fine. One issue is that I must have IS_TRUSTWORTHY set to ON. At this point I'm concerned that changing the IS_TRUSTWORTHY bit is opening some security concerns.?

    A little background on the nature of the jobs. We are moving to SQL Server 2012 and users that own the current jobs are sysadmins. The jobs are basically designed to load data (via linked server) from a remote source, so the jobs involve: drops, select into, etc. Moving forward the users access will be reader/writer and will still own the jobs. I am able to get the jobs to run if I use the execute as and set IS_Trustworthy ON.

    Any input would be greatly appreciated.

    ALTER PROCEDURE [dbo].[sp_Load_Data_IsuReview]

    WITH EXECUTE AS 'sql_job_test'

    AS

    BEGIN

    SET NOCOUNT ON;

    -- ***************************************** ;

    -- * Load ITARC, Seladcom, and UAMILT Data * ;

    -- ***************************************** ;

    if exists(select table_name from information_schema.tables

    where table_name = 'YS' and table_schema = 'dbo')

    drop table YS

    -- ****************** ;

    -- * Young Scholars * ;

    -- ****************** ;

    SelectEmplId = Common_Id,

    Adm_Appl_Nbr,

    Comment_DtTm,

    Comments

    Into YS

    From [sql\db1].ps.dbo.Ps_Occ_Cmnt_Ad_Vw

    Where Cmnt_Category = 'NYSPGM'

  • September 16, 2014 at 12:44 pm

    #1745877

    Hey Buck,

    Execute As -- So, here is the short description: It will work for the SPID it is created for.

    An example: When you open a tab for a new query you get spid (59) next one is something like (60) right? Basically when you use the Execute As that will work only in the scope of the "tab" it is called in.

    So for your use case: If you place that code as part of the job, that will persist for the job itself.

    The only issue you MIGHT run into is that Execute As not lasting as long as you want, in other words getting out of scope.

    Let me know if I can clarify anything!

    Jeremy

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

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