SQL copying folder and keeping security settings

Posted on 2009-05-27
Medium Priority
Last Modified: 2012-05-07
How do I copy a folder structure via an SQL SP and keeps the same security settings on the destination folders as on the source folders ?
When i make a copy now its security settings is always inherited from the parent security settings of the directory parent - i would like it remain as the original folder structure.
Right now im using this code to copy the folder structure :
DECLARE @Source 	VARCHAR(4096),
DECLARE @Destination	VARCHAR(4096),
SET @Source = '\\\test\folderstructure'
SET @Destination= '\\\test\customer\structure'
EXEC sp_OACreate 'Scripting.FileSystemObject', @FsObjId OUTPUT
EXEC sp_OAMethod @FsObjId, 'CopyFolder', NULL, @Source, @Destination, @OverWrite

Open in new window

Question by:torbenus
  • 3
LVL 31

Expert Comment

ID: 24489478
well, you can use "XCOPY" command of DOS in SQL server to copy folder, it's file and subdirectory to other location.  You can use any of the DOS command by XP_cmdshell of SQL SERVER. You can have a liitle look of it from my blog link given below.


Now, as long as security concern, it won't same as the source folder for SURE. there is no direct way to do so. you have to set proper permission for parent folder of destination so it will be inherited in the files and folder your copied, or write some script in .NET or may be in SQL Server which set the permission of file and folder after your copy it.
LVL 52

Expert Comment

by:Mark Wills
ID: 24492912
Could try movefolder - it might preserve the original ownership, however, I am fairly sure that it is going to inherit any conlicting ownership from it's parent.

Look at : http://msdn.microsoft.com/en-us/library/1c87day3(VS.85).aspx  for folder object properties
and then : http://msdn.microsoft.com/en-us/library/5tx15443(VS.85).aspx

I cannot recall any off the top of my head (and I am a fond user of OA), the scripting runtime libraries are not overflowing with information...

You may need to look outside the file or folder object and look at AD or other security pradigms.
Such as http://support.microsoft.com/default.aspx?scid=kb;EN-US;269159

Hope that helps...
LVL 52

Expert Comment

by:Mark Wills
ID: 24492941
LVL 38

Accepted Solution

Jim P. earned 2000 total points
ID: 24494308
Actually the trick is robocopy (Robust Copy) from MS. You'll have to use the xp_cmdshell to do it though.

How to Use Robocopy to copy security information without copying any file data
LVL 52

Expert Comment

by:Mark Wills
ID: 24494912
Yes of course - good call jimpen. It will mean enabling xp_cmdshell which can be secure if managed, though, found most people play with OA to avoid xp_cmdshell type operations.

Featured Post

Build your data science skills into a career

Are you ready to take your data science career to the next step, or break into data science? With Springboard’s Data Science Career Track, you’ll master data science topics, have personalized career guidance, weekly calls with a data science expert, and a job guarantee.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
In this article, we will show how to detach and attach a database and then show how to repair a corrupt database and attach it, If it has some errors. We will show how to detach and attach using SSMS or using T-SQL sentences.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how the fundamental information of how to create a table.

624 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question