For a security reason, I have created some users to have a permit to execute scheduled jobs only. So, given some required permissions in the msdb database, Even though users are not able to execute scheduled jobs in SQL Server. Below are the agents database roles are given for msdb database.
Even have above database roles in msdb database to execute scheduled jobs, users received following error when tried to run a job.
Even have above database roles in msdb database to execute scheduled jobs, users received following error when tried to run a job.
The EXECUTE permission was denied on the object ‘sp_start_job’, database ‘msdb’, schema ‘dbo’.After finding a solution with online reference, found a script to check required permissions of users. Below is a script used to check for same.
USE msdbFinally, I saw EXECUTE permission was denied on SQLAgentUserRole and TargetServersRole roles over sp_start_job system stored procedure in msdb database. I granted it and it works finally. I would like you to share your experience of such relevant errors.
GO
SELECT
PR.NAME,
DP.PERMISSION_NAME,
DP.STATE_DESC
FROM SYS.DATABASE_PERMISSIONS DP
JOIN MSDB.SYS.OBJECTS O
ON DP.MAJOR_ID = O.OBJECT_ID
JOIN SYS.DATABASE_PRINCIPALS PR
ON DP.GRANTEE_PRINCIPAL_ID = PR.PRINCIPAL_ID
WHERE O.NAME = 'SP_START_JOB'
GO
No comments:
Post a Comment
Please Use Good Leanguage