jQuery Validation Plugin: Remote Validation With AJAX (3/4)

Using PowerShell-FTP-Client to download and delete files from an FTP server

1. Configure PowerShell - out the scope of this blog entry, but here with Microsoft
2. Download and install the PowerShell FTP Client Module by Michal Gajda.
3. Use the code below which logs on to the FTP server and then gets a list of files
4. Downloads each file in the list
5. Deletes the file after download

$FtpServer = "ftp.server.com"
$User = "username"
$PWD = "cleverpassword"
$Password =  ConvertTo-SecureString $Pwd -AsPlainText -Force
$FtpCredentials = New-Object System.Management.Automation.PSCredential ($User, $Password)
Set-FTPConnection -Credentials $FtpCredentials -Server $FtpServer -Session MyFtpSession -UsePassive 
$FtpSession = Get-FTPConnection -Session MyFtpSession
$ServerPath = "/backups/test/"
$LocalPath  = "C:\Data\test\"
$fileList   = Get-FTPChildItem -Session $FtpSession -Path $ServerPath -Filter *.txt
foreach ($element in $fileList ) {
 $filename = $ServerPath  + $element.name
 Get-FTPItem    -Path $filename -Session $FtpSession -LocalPath $LocalPath -Overwrite $true
    Remove-FTPItem -Path $filename -Session $FtpSession 
}

How to allow a user to run a SQL Server Agent Job from a client application.

I wanted to allow users to run a SQL Server agent Job from a client application. Short Answer CALL msdb.[dbo].[sp_start_job] I had a number of errors but the fix was to 1) Add their login as a user to the msdb database 2) Put the user into group: SQLAgentOperatorRole
Long answer I had a stored procedure like below

CREATE PROCEDURE [dbo].[startJob] @JobName nvarchar(255) as
BEGIN
 declare @retval int
 EXEC @retval = msdb..sp_start_job @JobName
 return @retval 
END

And runs OK for me but didnt work for a user. Problem 1 "the execute permission was denied on the object 'sp_start_job' database 'msdb'" To check this permission run this query

SELECT PR.NAME, DP.PERMISSION_NAME, DP.STATE_DESC
FROM MSDB.SYS.DATABASE_PERMISSIONS DP
JOIN MSDB.SYS.OBJECTS O ON DP.MAJOR_ID = O.OBJECT_ID
JOIN MSDB.SYS.DATABASE_PRINCIPALS PR
ON DP.GRANTEE_PRINCIPAL_ID = PR.PRINCIPAL_ID
WHERE O.NAME = 'SP_START_JOB'

Results
NAME                 PERMISSION_NAME STATE_DESC
TargetServersRole EXECUTE         DENY
SQLAgentUserRole EXECUTE         GRANT

USE [msdb]
GO
CREATE USER [domain\securitygroup] FOR LOGIN [domain\securitygroup]
GO
EXEC sp_addrolemember N'SQLAgentUserRole', N'domain\securitygroup'
GO

Then I got

Server: Msg 14262, Level 16, State 1, Procedure sp_verify_job_identifiers, Line 61
The specified @job_name ('My Job X') does not exist.

This is because the user did not have permission to run someone else's job. They need to be made a member of the SQLAgentOperatorRole to do that.

EXEC sp_addrolemember N'SQLAgentOperatorRole', N'domain\securitygroup'
GO

References: