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.
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 @FsObjId INTEGER
DECLARE @Source VARCHAR(4096),
DECLARE @Destination VARCHAR(4096),
SET @Source = '\\192.168.0.1\test\folderstructure'
SET @Destination= '\\192.168.0.2\test\customer\structure'
EXEC sp_OACreate 'Scripting.FileSystemObject', @FsObjId OUTPUT
EXEC sp_OAMethod @FsObjId, 'CopyFolder', NULL, @Source, @Destination, @OverWrite
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...
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...
Also, just found this little gem : http://www.microsoft.com/downloads/details.aspx?FamilyID=5ca08634-5f13-42fb-998e-34015ccf1ba0&displaylang=en might help...
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
http://www.sqlhub.com/search?q=xp_cmdshell
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.