The EXECUTE permission was denied on the object ‘sp_start_job’, database ‘msdb’, schema ‘dbo’. - Error in SQL Server by serverku

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.

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 msdb
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

Finally, 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.

Another way to list out running scheduled jobs - SQL Server by serverku

We have been discussing the way to get scheduled jobs which are executing. Also wanted to tell you that this way is just similar to an earlier post with the same subject, but this an another alternate way. Please read my earlier relative posts for the same. You may aware of the dbo. sp_help_job which also help us they get the same information which you may read my one of the recent post.

Here I am sharing one more way to get the same information, where need to use sp_get_composite_job_info system object from msdb database and pass @execution_status parameter and value should be 1 for executing state.

USE msdb
GO

EXEC dbo.sp_get_composite_job_info @execution_status=1;

This will output all jobs which are currently executing. But when you run EXEC msdb.dbo.sp_get_composite_job_info without any parameter, it will give all the jobs and status as well. And the state values are following,

0 = Not idle or suspended,
1 = Executing,
2 = Waiting For Thread,
3 = Between Retries,
4 = Idle,
5 = Suspended,
6 = WaitingForStepToFinish,
7 = PerformingCompletionActions

You can pass any parameter values to get the jobs having that state. We all know about the query to get enabled\disabled scheduled jobs using db.sysjobs system table and having enabled = 1, but the same information we can know using msdb.dbo.sp_get_composite_job_info as following,

USE msdb
GO

-- Enabled jobs
EXEC dbo.sp_get_composite_job_info @enabled = 1;

-- Disabled jobs
EXEC dbo.sp_get_composite_job_info @enabled = 0; 

There are some other parameters which also can be used with this object like job_id, job_type etc. You may aware of this sp and may be used. Please suggest any other way using this object.

When exactly scheduled jobs started and completed - SQL Server by serverku

Some days ago, I worked to get failed job detail and an alert for the same. I used some system tables from the msdb database to fetch the details for the same. And msdb.dbo.sysjobhistory is one of the table among them, which used in the script. But this table doesn't contain data which shows the date and time when the job exactly started and completed. There are some fields which help us to get the same details,

• run_date
• run_time
• run_duration

Let us run the query and check data from table,

USE msdb
GO

SELECT TOP 3 job_id, 
             run_date, 
             run_time, 
             run_duration 
FROM   msdb.dbo.sysjobhistory WITH (nolock) 
ORDER  BY run_date DESC, 
          run_time DESC 

To get these details in shape and more readable format as we will use run_date, run_time and run_duration and convert them to date time format to achieve Start and Finish datetime. We will also use one more system table dbo.sysjobs for the job name and following is the script and output too,

USE msdb 
GO 

SELECT TOP 3 
 sj.job_id                 AS JobId, 
 sj.name                   AS JobName, 
 run_date                  AS RunDate, 
 run_time                  AS Runtime, 
 run_duration              AS RunDuration, 
 CAST(
  CONVERT(CHAR(8), run_date, 112) + ' ' + 
  STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(8), run_time), 6), 5, 0, ':'), 3, 0, ':')
  AS DateTime)     AS StartedAt,     
 (CONVERT (VARCHAR,
  (DATEADD(ss,
   (CASE LEN(run_duration)
    WHEN 1 
    THEN run_duration
    WHEN 2 
    THEN run_duration
    WHEN 3 
    THEN (CAST(Left(right(run_duration,3),1) as int)*60) 
     + (right(run_duration,2))
    WHEN 4 
    THEN (CAST(Left(right(run_duration,4),2) AS int)*60) 
     + (right(run_duration,2)) 
    WHEN 5 
    THEN (CAST(Left(right(run_duration,5),1) AS int)*3600) 
     + (CAST(Left(right(run_duration,4),2) AS int)*60) 
     + right(run_duration,2) 
    WHEN 6 
    THEN (CAST(Left(right(run_duration,6),2) AS int)*3600) 
     + (CAST(Left(right(run_duration,4),2) AS int)*60) 
     + right(run_duration,2)
     END ),
   CAST(
    CONVERT(CHAR(8), run_date, 112) + ' ' + 
    STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(8), run_time), 6), 5, 0, ':'), 3, 0, ':')
   AS DateTime)))
  ,120))     AS CompletedAt  
 FROM   msdb.dbo.sysjobhistory sjh WITH (nolock) 
        INNER JOIN msdb.dbo.sysjobs sj WITH (nolock) 
               ON sjh.job_id = sj.job_id 
WHERE  sjh.step_id = 0 
ORDER  BY run_date DESC, 
          run_time DESC 

(Click on image to enlarge)


Here we have all the details which we need. Hope this help you. Stay tuned for more posts.

The EXECUTE permission was denied on the object 'xp_sqlagent_enum_jobs', database 'mssqlsystemresource', schema 'sys' - SQL Server by serverku

Recently, while working with one query to get schedule job status, I used xp_sqlagent_enum_jobs object which gives details of state values of all jobs in msdb database and this is undocumented object. But received an error while running the following query for same,

EXECUTE master.dbo.xp_sqlagent_enum_jobs 1,'sa';

Error :

Msg 229, Level 14, State 5, Procedure xp_sqlagent_enum_jobs, Line 1
The EXECUTE permission was denied on the object 'xp_sqlagent_enum_jobs', database 'mssqlsystemresource', schema 'sys'.

As per error, says it does not have execute permission on mssqlsystemresource database for sys schema.

Solution :
For the solution just need to assign execute permission on dbo.xp_sqlagent_enum_jobs in the master database to user under the query to be run. So the following query needs to be run against master database,

USE master
GO
GRANT EXECUTE ON xp_sqlagent_enum_jobs TO test;

Hope this help you if you will receive such an error and you may enjoy this post. Stay tuned for further posts!

Failed to acquire connection. Connection may not be configured correctly or you may not have the right permissions on this connection - SQL Server SSIS by serverku

This week, I resolved one issue which I received continuously which is nothing but exactly displayed in the post title.  The issue occurred  when I ran ssis package with batch file or scheduled job and same error message raised.

Source: Start Execute SQL Task
   Description: Failed to acquire connection "<Connection Name>". Connection may not be configured correctly or you may not have the right permissions on this connection.

As per title, it seems an issue with connection of server configured in ssis package. I thought same because this type of error comes when server is not connected or it does not have proper and rights to connect. First, I checked all server connections and tried to resolve it, but don't succeed.

Please note I received this error only while running ssis package with scheduled job or batch file, But it was working fine while running ssis package directly. It means it is not an issue in ssis package, but it is with batch files or schedule job. After trying I visited one conversion which says it may be issued with 32 bit/64 bit mode. And here I configured size package with schedule job and batch file with servers having operating system 64 bit.

After changing ssis package with schedule jobs and batch file with 32 bits, it works fine for me. Let me share,

With Batch file,
I changed path of dtexec.exe from <drive>:\Program Files\Microsoft SQL Server\100\DTS\Binn to <drive>:\Program Files(x86)\Microsoft SQL Server\100\DTS\Binn like following batch file content,

"C:\Program Files (x86)\Microsoft SQL Server\100\dts\Binn\dtexec.exe" /FILE  "C:\SSIS\SSISTest\bin\testing.dtsx" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF  /REPORTING EWCDI 

With Scheduled job,
I am sharing one screen shot of schedule job step configuration of ssis package,

The day after this changed, I always see this package succeed. I am leaving one discussion open here “why it was failing with a 64 bit mode run?