Altering SQL Jobs without granting SysAdmin privilege

What would you do if you were asked to grant a few users the permission for altering SQL Jobs? It is a tricky task to carry out although many of us would think that granting SQLAgentOperatorRole role might be enough, however, many years ago no sooner had I done it than I realised it did not work as expected, and now I think most of us faced up this issue at first.
The SQLAgentOperatorRole role allows user to alter SQL jobs as long as the user is the owner of the SQL job, otherwise, the user need to be SysAdmin at SQL instance level. What’s more, one of the best security practices says that nobody but DBA must be SysAdmin, and we should use Windows Authentication. Nevertheless, when it comes to owners for SQL jobs they should use 'sa' as owner which does not mean having enable that account, it should be disable. In this sense, it is highly advisable to have the disabled account “sa” as the owner of all SQL jobs and avoid granting SysAdmin privilege. So, it is of paramount importance not to use a windows user as owner of a SQL job because SQL Server will always validate windows users against the Active Directory and it is likely to get unforeseen errors during that process. 
Today I am going to share with you a stored procedure to enable users to alter SQL Jobs without the need of granting SysAdmin privilege. This stored procedure consists of a logic that will allow a specific user to take the ownership of a SQL Job so that the user can be able to alter it and after making the changes the user can change the ownership to ‘sa’ (or the original owner). This is the stored procedure that I mentioned above.

USE [msdb]
GO
CREATE PROC[dbo].[usp_change_owner_job] @jobname varchar(max), @newowner varchar(max)
with execute as owner
as
begin
    declare @old_owner varchar(max)
    select @old_owner= s.name from msdb.dbo.sysjobs j inner join sys.server_principals s
    on j.owner_sid= s.sid where j.name=@jobname

    --declare @newowner varchar(max)
    --set @newowner=ORIGINAL_LOGIN()
    EXEC msdb.dbo.sp_update_job @job_name=@jobname, @owner_login_name=@newowner

    --print @newowner
    declare @msg varchar(max)
    set @msg= 'The owner of ' +@jobname + ' job was changed from ''' + @old_owner + ''' to ''' + @newowner + ''''
    print @msg    
end

So, for instance, if you have a SQL job whose owner is 'sa' and your user is 'User2', you will not be able to alter the job until you take the ownership of it temporarily. Here are all the steps you must follow:

1. Create the stored procedure 'usp_change_owner_job' based on the code above.
2. Grant EXECUTE permission on 'usp_change_owner_job' to 'User2'.
3. Grant SQLAgentOperatorRole role to 'User2'.
4. Change the ownership of the SQL Job to 'User2' by using the stored procedure 'usp_change_owner_job'.

EXEC msdb.dbo.[usp_change_owner_job] 'BusinessJob01', 'User2'

The owner of BusinessJob01 job was changed from 'sa' to 'User2'

5. Now the User2 has the ownership of the SQL job and is now able to alter it.
6. After making the changes on the SQL Job, the 'User2' must change the ownership to 'sa'.

The owner of BusinessJob01 job was changed from 'User2' to 'sa'

The owner of BusinessJob01 job was changed from 'User2' to 'sa'

Having successfully completed all the steps will you be able to alter any SQL Job without granting SysAdmin privilege to users. That is all for now. Let me know any remarks you may have.

Getting the full name of SQL Jobs including the steps in execution

While monitoring a database server, we may need to know some details about the sessions, connections and requests that migh be causing performance or blocking issues so that we can take actions to fix them. In doing so, one very important piece of information is the program name that is connected to the database engine. Broadly, it is posible to see the program name in detail by using system stored procedures or DMVs such as 'sp_who2' and 'sys.dm_exec_sessions'. Nevertheless, not every name of the programs may be easy to interpret, especially when it comes to SQL Jobs. For instance, if you detected that a SQL Job is the root of the problem and then needed to know which SQL Job it is, the column program_name of 'sp_who2' or 'sys.dm_exec_sessions' woud only give us the SQL Job ID based on the following format:

SQLAgent - TSQL JobStep (Job 0x2613DA812CD2D248A9BA377DE6DEF355 : Step 1)

Obviously, we cannot do much with that info because there is no SQL Job name, and even worse, no SQL Job step name. However,  we can figure out the name of the SQL Job in msdb.dbo.sysjobs by using the ID.

SELECT * FROM msdb.dbo.sysjobs WHERE job_id=0x2613DA812CD2D248A9BA377DE6DEF355

Despite the fact that it may be relativily easy to get the name of the SQL Job, it is not enough because it is of paramount importance to know the name of the step in execution, and keep in mind that doing this manually this every time when needed is going to be an uphill battle as it is arduous and not optimal, especially if there are many SQL Jobs running and causing struggles. Thinking of this situation, I created a script to automate the task of figuring out details related to SQL Jobs such as the name and also the step name that is running. To be more precise, this logic is inside a function called "ufn_GetJobStepNameDesc" that takes the value of the "program_name" column and returns the name of the SQL Job and the step in execution. Let's take a look at the following code whereby we also filter out the sessions used by SQL Jobs.

SELECT session_id, login_time, login_name, [status], writes,

       logical_reads, [language], DB_NAME(database_id) DatabaseName,

          dbo.ufn_GetJobStepNameDesc([program_name]) AS SQLJobDescription

FROM sys.dm_exec_sessions where [program_name] like 'SQLAgent - TSQL%'

As you can see, I have highlighted the use of the function, and also added other important columns to look at as part of monitoring. So, using dbo.ufn_GetJobStepNameDesc([program_name]) the final outcome would be like this: SQLAgent - TSQL JobStep "<Name of the job step>" (Job: <Job name>).  For instance: SQLAgent - TSQL JobStep "Updating_Accounts" (Job: SAP_Financial_Process)

Here I share with you my script so that you can check it thoroughly and then make the most out if it.

USE [master]

GO

CREATE FUNCTION  [dbo].[ufn_GetJobStepNameDesc] (@step_name_desc VARCHAR(MAX))

RETURNS VARCHAR(max)

AS

BEGIN

       DECLARE @full_step_name_desc VARCHAR(MAX)

       DECLARE @jobstep_id_start INT

       DECLARE @jobstep_id_len INT

       DECLARE @jobstep_id INT

       SELECT @jobstep_id_start=CHARINDEX(': Step', @step_name_desc)+7, @jobstep_id_len=CHARINDEX(')', @step_name_desc)-@jobstep_id_start

       set @jobstep_id= CAST(SUBSTRING(@step_name_desc, @jobstep_id_start,@jobstep_id_len) AS INT)

       DECLARE @job_id_start INT

       DECLARE @job_id_len INT

       DECLARE @hexa_job_id VARBINARY(MAX)

       SELECT @job_id_start=CHARINDEX('(Job 0', @step_name_desc)+5,  @job_id_len=CHARINDEX(':', @step_name_desc)-@job_id_start

       SET @hexa_job_id=CONVERT( VARBINARY, RTRIM(LTRIM(SUBSTRING(@step_name_desc, @job_id_start,@job_id_len))),1)

      

       SELECT @full_step_name_desc='SQLAgent - TSQL JobStep "' + step_name+'" (Job: ' + j.[name] +')'

       FROM msdb.dbo.sysjobsteps js

            INNER JOIN msdb.dbo.sysjobs j ON j.job_id=js.job_id

       WHERE step_id=@jobstep_id and j.job_id=CAST(@hexa_job_id  AS UNIQUEIDENTIFIER)

       RETURN ( @full_step_name_desc)

END

That is all for now, I hope you find this post helpful. Let me know any remarks you may have. Stay tuned.

Why is the .txt history detail file of SQL Backup Job not created?

Clearly, we always need to have some kind of history for SQL Backup jobs in order to check whether or not they were executed correctly. I personally use a .txt file to write on it all details of each step executed so that I can use it to diagnose any problem or error behind it. Nevertheless, at times set it up may become something not so easy as the .txt file might not be created during the SQL Backup job execution.


There are some reasons why this may be happening. The first one is because the directory where the .txt file does not exist. It should have been created manually before executing the SQL Backup job. The second reason is because the backup directory is blocked. Surprisingly, you will realise it is with blocked access when you try to open it. One way to get it unblocked is by simply opening the directory and then clicking on “continue” via Windows Explorer.



The third one is because the SQL Agent account does not have permissions on that directory. There must have given Read and Write permission on it and there are some cases where we will need to give explicit permissions to the SQL Agent account on that directory via CMD windows command tool. Undoubtedly, it would be no problem if we are working on only one directory but what would it happen whether we are implementing many SQL Backup jobs? it would become a very tedious job to manage one by one. So, in this case we need some manner to automatize and get them done rapidly. For instance, here I am going to show you a technique to achieve it:

icacls "H:\SQLBackup\FinancialDB\Full" /grant MyDomain\sqlagentAccount:(OI)(CI)F

Now for creating the script to give permission on all necessary directories we can create the code by using this T-SQL (based on reading the directory from Backup Devices whose names are like 'FinancialDB-Full_Database_Backup'):

DECLARE @sn NVARCHAR(128);
EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE', 'SYSTEM\CurrentControlSet\services\SQLSERVERAGENT',  'ObjectName',   @sn OUTPUT;
select 'icacls "'+SUBSTRING(physical_name,1, LEN(physical_name)-CHARINDEX('\',REVERSE(physical_name)))+'" /grant '+@sn+':(OI)(CI)F'
from sys.backup_devices where name
ORDER BY name 

All the output results must be executed on CMD tool (as Administrator) and finally after executing the SQL Backup job we will verify that the .txt file was created:

And the history details are inside:


I hope this tip is useful and practical for you. Let me know any remark you may have. Thanks for reading!

Reusing SQL Job creating script to create new similar ones with different Schedule ID

As we now it is very important to look for new ways of being more productive every day. For instance, one of our tasks as DBA is to implement SQL Backup Jobs for each database. Personally, I like reusing code to create more similar SQL Jobs faster, that is, create one SQL Job, generate the SQL creating script of it, replace some things, and finally execute it to create every SQL Backup Job for all databases.
After creating the next SQL Jobs by reusing the complete code, you will find that these SQL Jobs have the same SQL Schedule ID. So, if we modify the schedule for one of them, every SQL Job will be modified as well. Under this circumstance, we will have to drop the SQL Schedule and create a new one. It may not be what we wanted to do as it may take some additional time. Therefore, are we curious to know how to create SQL Jobs based on the same template but having a different SQL Schedule ID?. This post shows how to do it.

First of all, look at this picture.



You will see a parameter @schedule_uid which is the SQL Job schedule ID, so what we have to do now is to comment this line in order to allow SQL Server to generate a new ID for the SQL Job Schedule.



Having modified that parameter for each Job, the rest of Jobs will not inherent the Schedule ID anymore and a new one will be created instead. I hope this post is useful for you and let me know any questions. Until next post, thanks for reading!

Did you get this "AuthorizationManager check failed" error working with SQL Jobs and PowerShell?

Taking of PowerShell, while working on implementing SQL Jobs which execute PowerShell scripts, unexpectedly, they may begin failing without any apparent reason. So, we get this error:

AuthorizationManager check failed At line:1 char:2  + & <<<<  ‘S:myfolderscript.ps1’      + CategoryInfo          : NotSpecified: (:) [], PSSecurityException      + FullyQualifiedErrorId : RuntimeException.  Process Exit Code 1.  The step failed.

What we should do is to check PowerShell so as to make sure the ExecutionPolicy is not set to “Restricted” by executing the following command:

Get-ExecutionPolicy

If it is then set it to “RemoteSigned” or “Unrestricted” depending on your security policy.

Set-ExecutionPolicy RemoteSigned

Not only do we have to make sure that Windows Management Instrumentation service (WMI) service is enabled and running, but also we have to restart it. Only after successfully doing that will your job and script run again with no error. I do believe that this would work without a shadow of a doubt for the vast majority of cases. I hope you find this post useful. Thanks for reading.

How to schedule a SQL Job to run every second in SQL Server 2005

It is well known that it is not possible to schedule a SQL Job to run every second or some seconds in SQL Server 2005 by using the wizard (I mean less than one minute). Nevertheless, when there is a need to do it we may have one way to achieve it. Let me expand on what I am saying.  Firstly, we have to create the SQL Job in SQL Server 2008 by using the Wizard and then we need to generate the script to create the SQL Job in SQL Server 2005.

Having successfully completing that will you be able to have a SQL Job in SQL Server 2005 to run every second with any hitches. I hope you find this tip very useful for you. That is all for now. Thanks for reading. Stay tuned.