With Execute AS

• dwilliscp

  SSCrazy Eights

  Points: 9270

  More actions

  February 8, 2018 at 2:19 pm

  #327242

  We moved our application over from 2008R2.... set up the sysadmin Active Directory group.. but the below code no longer works.

  I even commented out the sp_delete_job and had it do a simple select..

  ALTER PROCEDURE [dbo].[DeleteJob]

  
  

  @Namenvarchar(260)

   

  WITH EXECUTE AS 'Domain\DB' --This needs to be uncommented and a proper sysadmin user added that is a dbo in msdb.

  AS

  print 'hi'

  select * from msdb.dbo.sysjobs

  ---- execmsdb.dbo.sp_delete_job @job_name = @name

  What I get is :
  

  hi

  Msg 229, Level 14, State 5, Procedure DeleteJob, Line 166

  The SELECT permission was denied on the object 'sysjobs', database 'msdb', schema 'dbo'.

  However when I go into msdb and run the select.. it works... and I am part of Domain\DB

• Sue_H

  SSC Guru

  Points: 90860

  More actions

  February 8, 2018 at 2:37 pm

  #1979118

  dwilliscp - Thursday, February 8, 2018 2:19 PM

  We moved our application over from 2008R2.... set up the sysadmin Active Directory group.. but the below code no longer works.

  I even commented out the sp_delete_job and had it do a simple select..

  ALTER PROCEDURE [dbo].[DeleteJob]

  
  

  @Namenvarchar(260)

   

  WITH EXECUTE AS 'Domain\DB' --This needs to be uncommented and a proper sysadmin user added that is a dbo in msdb.

  AS

  print 'hi'

  select * from msdb.dbo.sysjobs

  ---- execmsdb.dbo.sp_delete_job @job_name = @name

  What I get is :
  

  hi

  Msg 229, Level 14, State 5, Procedure DeleteJob, Line 166

  The SELECT permission was denied on the object 'sysjobs', database 'msdb', schema 'dbo'.

  However when I go into msdb and run the select.. it works... and I am part of Domain\DB

  If you check the information on the arguments in the documentation for Execute As, it states:
  name must be a singleton account, and cannot be a group, role, certificate, key, or built-in account, such as NT AUTHORITY\LocalService, NT AUTHORITY\NetworkService, or NT AUTHORITY\LocalSystem.
  EXECUTE AS (Transact-SQL)
  
  Sue

• dwilliscp

  SSCrazy Eights

  Points: 9270

  More actions

  February 12, 2018 at 11:42 am

  #1979481

  Thanks for the help.