SQL copying folder and keeping security settings

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

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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.
Mark WillsTopic AdvisorCommented:
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...
Mark WillsTopic AdvisorCommented:
Jim P.Commented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Mark WillsTopic AdvisorCommented:
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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.