February 8, 2018 at 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
February 8, 2018 at 2:37 pm
dwilliscp - Thursday, February 8, 2018 2:19 PMWe 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
February 12, 2018 at 11:42 am
Thanks for the help.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply