May 19, 2017 at 3:20 pm
Hi SSC,
We have a major PITA happening. Our company leadership in their infinite wisdom has determined that NO ONE except Database ADMINISTRATORS can have sysadmin even on a DEV server. Since our processes are all ETL that run to a datamart on a monthly schedule, we use SQL Agent jobs that run under a service account owner (and SSIS proxy as applicable for SSIS job steps). All developers are in SQLAgentOperator role on MSDB. We are UNABLE to DEPLOY jobs to run with owner = service account or change owner on jobs that are deployed . So the developer is forced to every single time they need to do something, they have to open a request for the DBA to do it. A proper unit test of a job needs to run under the SERVICE account, not the DEVELOPER credentials. And as an added bonus, our RELEASE coordinators also cannot deploy SQLAgent jobs when releasing to SIT/PROD. We have to have DBA do it.This is causing quite a bottleneck in our unit testing and deployments. We are very busy. .
Is there any hidden way to make this happen? I have checked boards and BOL and I can't find anything except that only sysadmin can do it. Our DBAs have ABSOLUTELY no problem with the DEV team creating their own jobs on DEV servers. This is just busywork for them to constantly have to deploy them so the developers can test. They are also very busy.
Has ANYONE discovered a way around this, so the we can let the development team (all in Active Directory group) be able to deploy on the DEV server? WE have different service account for prod / non-prod so we USED to be able to script just do this (in msdb job script to create the job).
DECLARE @ServiceAccount @ServiceAccount NVARCHAR(100)
EXEC @ServiceAccount @ServiceAccount = [Utility].[dbo].fn_getService_account /*returns service account*/
/*...category etc...*/
DECLARE @jobId BINARY(16)EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'Adhoc_JIRA_2504',
@enabled=1,
@notify_level_eventlog=0,
@notify_level_email=0,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N'No description available.',
@category_name=N'[Uncategorized (Local)]',
@owner_login_name=@ServiceAccount,/*THIS*/
@job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
May 22, 2017 at 7:29 am
Adding the user to SQLAgentOperator under msdb database will allow user to create own jobs. Not sure whether he will be able to access the package files in that machine.
May 22, 2017 at 7:39 am
You could set up a daily job (or whatever frequency is appropriate) to look for jobs that aren't owned by the SQL Server Agent service account, and change them so that they are. You'd want to build in some safeguards so that jobs that are not intended to be thus promoted are not inadvertently elevated to be owned by the SQL Agent account.
John
May 22, 2017 at 10:11 am
VastSQL, thanks but they already are in SQLAgentOperator. The problem is that they cannot change the job owner to the service account.
John MItchell: This looks like maybe what we will have the DBA do then the DEV users can run it when needed. Thanks for thinking about it. I'll see if we can come up with something.
May 22, 2017 at 10:45 am
why not have a new DBA job that now updates any jobs not owned by [sa] to be [sa], and any job steps that are of type SSIS to have your proxy operator instead of whatever was the default.
I do something similar already, where any job that does not have a notify operator gets assigned the default 'DBA Team' I've created. if that job runs once a day or so, it would resolve your issue if encountered.
here's a craptastic example of a procedure i call as a job step:IF OBJECT_ID('[dbo].[sp_fix_joboperators]') IS NOT NULL
DROP PROCEDURE [dbo].[sp_fix_joboperators]
GO
--#################################################################################################
--This job makes sure the default operator exists, and for any jobs that exist that do not
--have a notification operator by email in place, change the job to have the default operator
--#################################################################################################
CREATE PROCEDURE sp_fix_joboperators
AS
BEGIN
--discover the standard operator
DECLARE
@OperatorName nvarchar(128) = N'DBA Team',
@OperatorEmail nvarchar(128) = N'DBATeam@mydomain.org'
-- Created By: Lowell Izaguirre
-- Create Date: 03/06/2014
--#################################################################################################
-- Registered Server Script
--Find any jobs where the notify operator is not selected
--#################################################################################################
SELECT
job_id,name
FROM msdb.dbo.sysjobs
WHERE notify_email_operator_id = 0
--#################################################################################################
--guarantee our default notification group exists
--#################################################################################################
IF NOT EXISTS(SELECT * FROM msdb.dbo.sysoperators WHERE name = @OperatorName)
BEGIN
EXEC msdb.dbo.sp_add_operator @name=@OperatorName,
@enabled=1,
@weekday_pager_start_time=90000,
@weekday_pager_end_time=180000,
@saturday_pager_start_time=90000,
@saturday_pager_end_time=180000,
@sunday_pager_start_time=90000,
@sunday_pager_end_time=180000,
@pager_days=0,
@email_address=N'somegroup@mydomains.org;lizaguirre@mydomain.org',
@category_name=N'[Uncategorized]';
END
--#################################################################################################
--reset the email to be the company distribution list instead of a list of individuals
--#################################################################################################
IF EXISTS(SELECT * FROM msdb.dbo.sysoperators WHERE name=@OperatorName AND email_address <> N'somegroup@mydomains.org;lizaguirre@mydomain.org')
BEGIN
EXEC msdb.dbo.sp_update_operator @name=@OperatorName,
@enabled=1,
@pager_days=0,
@email_address=N'somegroup@mydomains.org;lizaguirre@mydomain.org',
@pager_address=N'',
@netsend_address=N''
END
--#################################################################################################
--remove/replace what was an old operator example
--#################################################################################################
--IF EXISTS(SELECT * FROM msdb.dbo.sysoperators WHERE name = 'DBA')
-- BEGIN
-- EXEC msdb.dbo.sp_delete_operator
-- @name = 'DBA',
-- @reassign_to_operator = 'DBA Team';
-- END
--#################################################################################################
--modify all existing jobs to use our default notification group, if one is not assigned.
--#################################################################################################
DECLARE
@imodel VARCHAR(MAX),
@isql VARCHAR(MAX),
@jobid VARCHAR(40),
@jobname VARCHAR(256)
SET @imodel = '
--Job Name:[TheJobName]
EXEC msdb.dbo.sp_update_job
@job_id = N''[TheJobID]'',
@notify_level_email = 2,
@notify_level_netsend = 2,
@notify_level_page = 2,
@notify_email_operator_name = N''[TheOperator]'';'
DECLARE c1 CURSOR FOR
--SELECT
-- job_id,name
--FROM msdb.dbo.sysjobs
--WHERE notify_email_operator_id NOT IN(SELECT
-- id
-- FROM msdb.dbo.sysoperators
-- WHERE name = @OperatorName)
SELECT
job_id,name
FROM msdb.dbo.sysjobs
WHERE notify_email_operator_id = 0
OPEN c1
FETCH NEXT FROM c1 INTO @jobid,@jobname
WHILE @@FETCH_STATUS <> -1
BEGIN
SELECT
@isql = REPLACE(REPLACE(REPLACE(@imodel, '[TheJobID]', @jobid),'[TheJobName]',QUOTENAME(@jobname)),'[TheOperator]',@OperatorName)
PRINT @isql
exec(@isql)
FETCH NEXT FROM c1 INTO @jobid,@jobname
END
CLOSE c1
DEALLOCATE c1
END --PROC
GO
Lowell
May 22, 2017 at 1:06 pm
Thanks Lowell, that does more than we need but it is a good starting off place, not so much craptastic at all!. I also had the idea to let the dev team run it on-demand rather than in a schedule. They are SQLAgentOperator so that should work. We would have it also exclude any DBA maintenance jobs. I had thought to just have a @job_name as an input parameter, but then again whoever ran it could not alter it to add the parameter so that wouldn't work.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply