Deleting folder on a drive

Question

Deleting folder on a drive

richardmgreen1

SSChampion

Points: 10351
More actions
December 16, 2020 at 9:59 am

#3821082

Hi all

I'm using xp_create_subdir to create subfolder on one of our drives to stores files in.
Once the files have "expired", they get deleted.

I need to then delete the folder once it's empty as I don't want a load of empty folders just laying around (tidyness more than anything else).

I've seen a way of deleting the folders using cmdshell but I'm wary of enabling it because of the potential security issues.

Has anyone got a way deleting folders (I'll be checking it's empty before deleting it) witout using either PoSH or cmdshell?

Thanks

Richard

Viewing 15 posts - 1 through 15 (of 34 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246960 More actions · Answer 1

You can easily do this using SSIS.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

richardmgreen1 SSChampion Points: 10351 More actions · Answer 2

Thanks for that.

I origianlly wanted to do it via a stored procedure (to keep everything together, but now I've added a VB script-task to do the same thing.

Thanks agan

Richard

Phil Parkin SSC Guru Points: 246960 More actions · Answer 3

Well done. xp_cmdshell() would have allowed you to do this in your proc, of course.

A certain Mr Jeff Moden will probably tell you that the controlled enabling of xp_cmdshell poses little additional risk and yet greatly increases your solution options. I can't remember whether he wrote an article about exactly that, but if he did, it would be worth a read.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

richardmgreen1 SSChampion Points: 10351 More actions · Answer 4

I've got it sussed out now so I think I'll leave it well enough alone.

I've got an unusual error now that reads:-

Could not find a part of the path 'C:\Program Files (x86)\Microsoft Visual Studio\2019\Community\Common7\IDE\CommonExtensions\Microsoft\SSIS\150\Binn\Microsoft.SqlServer.Dts.Runtime.Variable'.

Just googling that now to see what's going on.

Phil Parkin SSC Guru Points: 246960 More actions · Answer 5

It may not be the case here, but spaces in path names are often problematic. Are you enclosing the path in quotes?

I'm also interested in knowing why you are trying to access that particular path – it does not seem a good place to store non-system files.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

richardmgreen1 SSChampion Points: 10351 More actions · Answer 6

It's only thrown that error now I've finished my script task (although it didn't throw an error in testing).

I'm passing the path I grab in an earlier item into a variable to the script task so it knows which folder to check.

If the folder is empty, it gets deleted. If it's not, it gets left alone.

It's a bit weird but I'll get to the bottom of it.

richardmgreen1 SSChampion Points: 10351 More actions · Answer 7

Found it.

I'm an idiot and I'd made a syntax error in my VB code.

All now appears to be working as it should.

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 8

You are aware that you can create an agent job that runs in the powershell sub system? Using that - you can easily setup a job to run on a schedule, associated with an appropriate proxy account (if needed) that just executes PS commands.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 9

richardmgreen1 wrote:

I've seen a way of deleting the folders using cmdshell but I'm wary of enabling it because of the potential security issues.

Heh... it's not the security risk that people have been made to believe. Consider this... if it's "disabled", who can turn it on? The answer is only those people that have "sysadmin" or "control server" privs. That can be anyone that has those privs including someone that hacks the login for anyone that has those privs.

Next, ask yourself who can use it (unless you've made the horrible mistake of grant direct use privs to non-sysadmin people)? It's only the same group of people that have "sysadmin" or "control server" privs.

Disabling it will not prevent a hacker from enabling it. Enabling it will not allow people outside the privileged group to use it. You're concentrating on the wrong things if you consider xp_CmdShell to be a security risk and losing out on a huge amount of good, solid functionality to keep it disabled.

If you're really concerned, have your proc enable it, use it, and then disable it. It won't do any good as I pointed out above but it will give nervous-nellies the nice warm fuzzies. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

richardmgreen1 SSChampion Points: 10351 More actions · Answer 10

@jeff - My understanding was that, once CmdShell was enabled, is was usable by anyone. Nice to know that's not the case.

There is a very finite list of people with SysAdmin privs and no-one with Control Server privs (I'm going to have to check that again now(!)).

@Jeffrey - I didn't want too use PoSH as I wanted to delete the files as part of the routines I already have in place (i.e. sprocs I've already written) rather than set up another job.

I'll do some more investigating and see what's what.

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 11

richardmgreen1 wrote:

@Jeff - My understanding was that, once CmdShell was enabled, is was usable by anyone. Nice to know that's not the case.

Oh my... now I understand why you were concerned. I didn't realize that bit mis-information was still making its rounds after more than 15 years. Thank you for the feedback. It'll help me help others now that I know.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

richardmgreen1 SSChampion Points: 10351 More actions · Answer 12

Hi Jeff

I think the miscommunication comes from Microsoft themselves (or maybe I've just not read enough into it). According to this page , it states that there are security implications to using it.

It may be that I've either mis-read or been overthinking it's use that's caused the issue.

Thanks again

Richard

Phil Parkin SSC Guru Points: 246960 More actions · Answer 13

...it states that there are security implications to using it.

Not quite. It mentions 'potential security implications' – and Jeff has described some of those already.

This reply was modified 3 years, 11 months ago by Phil Parkin.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

richardmgreen1 SSChampion Points: 10351 More actions · Answer 14

So I was overthinking it then.

Anyway, I've got the stored procedure working and it enables cmdshell, deletes the folder and disables it again.