Wednesday, 11 November 2015

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 
}

Tuesday, 10 November 2015

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:

Monday, 15 April 2013

Amazing video

This is an live action short video from Russia (I believe). It's in the style of a first person shooter game.

Tuesday, 5 February 2013

How to stop Excel from automatically splitting a CSV

One of the annoying features added to Excel is that when you open a file with csv extension it automatically parses the file on the "," and butchers your file. This is annoying if the file uses another character to split or contains German numbers which use a comma for the decimal etc. So how do you fix this? 1) Open Excel (dont double click on the CSV) 2) Go to "Data" -> "Get External Data from Text" 3) Select the CSV file 4) Click "Import" 5) Go through the wizard and specify the delimiter and that all fields are text not numebrs etc 6) You are done.

Friday, 23 November 2012

Learning Silverlight

I recently had to take over a Silverlight application and had to remind myself how it hangs together. Here are some good blogs on the topic

How to blog Code

Here is a great blog on how to post code snippets.
  1. http://www.craftyfella.com/2010/01/syntax-highlighting-with-blogger-engine.html
  2. Copy and paste source into http://www.opinionatedgeek.com/dotnet/tools/htmlencode/encode.aspx
  3. encode the source then copy from above step and paste into blog
  4. Then wrap in tags