How do I check job status without using msdb and master?
My client is using SQL 2012 and I was tasked with creating a stored procedure to start jobs. I added logic to first check the job status to be sure it was not already running. When it came time to migrate to a different environment the DBA said we could not use the databases msdb or master. How can I check job status and start a job without using those databases? Any help would be great. Also, what would the reason be behind not using those databases?
Those are system databases and the DBA is right to restrict permissions to them. He/she has a responsibility to the organisation to protect the server from being messed up. But, following the principle of 'just enough permissions to do your job' it should be possible to set up stored procedures that allow the appropriate user(s) to do this one thing without giving them the keys to everything. I suggest you limit the list of jobs they can start. You don't want a disgruntled emploee kicking off the wrong thing!