Starting a SQL Agent job with Powershell and Windows Scheduled Tasks

In the past, I’ve needed to start SQL Agent jobs using Windows Scheduled Tasks. Using Powershell, this is now easier than ever.

First, let’s create the Powershell script. It’s very basic and relies on Windows Authentication and SQL Native Client to connect to the SQL Server.

#Vars for Server and JobName
$Server = "<Server>"
$JobName = "<JobName>"

#Create/Open Connection
$sqlConn = new-object System.Data.SqlClient.sqlConnection "server=$Server;database=msdb;Integrated Security=sspi"
$sqlConn.Open()

#Create Command Obj
$sqlCommand = $sqlConn.CreateCommand()
$sqlCommand.CommandText = "EXEC dbo.sp_start_job N'$JobName'"

#Exec Command
$sqlCommand.ExecuteReader()

#Close Conneection
$sqlConn.Close()


In a nutshell, this script will need you to set 2 variables, $Server and $JobName. Once those have been set, the script will open up a connection to the server, execute the sp_start_job command and then close the connection.

Next, we want to start the Powershell script via Windows Scheduled Tasks. When setting up the task, choose "Start a program" under Action and type powershell.exe . We specify the Powershell script to execute in the additional arguments. By adding &'\\<filepath>\StartTest_SQL_AgentJob.ps1' Powershell will start up and execute the script.

Note: If your <filepath> has spaces in it, you will need the "&", otherwise it can be omitted.

Getting SQL Server Error Messages to Write to the Event Log

SQL Server alerts can give you valuable insight into issues with your servers and can track a multitude of different errors, messages and events. To view all the built-in system error messages, run the following script on Master database.

SELECT *

  FROM SYS.MESSAGES

 WHERE LANGUAGE_ID = 1033

1033 is the English language ID taken from the syslanguages table

SELECT MSGLANGID

  FROM SYSLANGUAGES

 WHERE NAME = 'US_ENGLISH'

In SQL Sever 2008 there are over 8900 different messages that can be returned from the server, all with varying severities and text messages. The event I’m going to focus on here is message_id 229, “The %ls permission was denied on the object '%.*ls', database '%.*ls', schema '%.*ls'”.

SELECT * FROM SYS.MESSAGES

 WHERE LANGUAGE_ID = 1033

   and message_id = 229

Message_id	Language_id	Severity	Is_event_logged	Text
229	1033	14	0	The %ls permission was denied on the object '%.*ls', database '%.*ls', schema '%.*ls'.

If I want to be alerted any time a user tries to call a stored procedure or query a table they dont have access to, I can create a SQL Agent Alert using the following procedure and further configuring the alert to email me.

EXEC msdb.dbo.sp_add_alert @name=N'Insufficient Permission Error',

            @message_id=229,

            @severity=0,

            @enabled=1,

            @delay_between_responses=0,

            @include_event_description_in=1,

            @category_name=N'[Uncategorized]',

            @job_id=N'00000000-0000-0000-0000-000000000000'

GO

After this is setup, you would expect that everytime this error is generated an email would be sent to you, but…… going back to the IS_EVENT_LOGGED column in the SYS.MESSAGES table, you see that this event is not logged and will not be sent to the Event Log.

So how do I get this error to log to the Event Log and then sent in an email? In SQL 2005 SP2+ and SQL 2008, the following command can be issued to modify the existing messages:

EXEC sp_altermessage

 @message_id = 229

,@parameter = 'WITH_LOG'

,@parameter_value = 'True'

Setting the parameter ‘With_Log’ to a value of true tells SQL that the triggering of this event will always result in the error being written to the Event Log

NOTE: If/when you decide that you no longer want to monitor this event, just disabling the alert will not be enough to keep the event from being logged to the Event Log. You must run the following script to keep it from being logged.

EXEC sp_altermessage

 @message_id = 229

,@parameter = 'WITH_LOG'

,@parameter_value = 'False'