how to script out all the permissions in a folder using powershell?

Question

Using the script below I find out where are the data and log folders:

    declare @rc int, 
    @dir nvarchar(4000) 

    declare @data    nvarchar(4000)
    declare @log     nvarchar(4000)
    declare @SQLPath nvarchar(4000)
    declare @Backup  nvarchar(4000)

    exec @rc = master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE',N'Software\Microsoft\MSSQLServer\Setup',N'SQLPath', @dir output, 'no_output' 
    select @SQLPath = @dir

    ----------------------------------------------------------------
    --For the default data location: 
    ----------------------------------------------------------------

    exec @rc = master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE',N'Software\Microsoft\MSSQLServer\MSSQLServer',N'DefaultData', @dir output, 'no_output' 


    if (@dir is null) 
    begin 
    exec @rc = master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE',N'Software\Microsoft\MSSQLServer\Setup',N'SQLDataRoot', @dir output, 'no_output' 
    select @dir = @dir + N'\Data' 
    end 

    select @data = @dir

    ----------------------------------------------------------------
    --For the default log location: 
    ----------------------------------------------------------------
    exec @rc = master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE',N'Software\Microsoft\MSSQLServer\MSSQLServer',N'DefaultLog', @dir output, 'no_output' 


    select @log = @dir


    ----------------------------------------------------------------
    --For the default Backup location: 
    ----------------------------------------------------------------
    exec @rc = master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE',N'Software\Microsoft\MSSQLServer\MSSQLServer',N'BackupDirectory', @dir output, 'no_output' 


    select @Backup = @dir

    SELECT  
    [data path] =     @data, 
    [log path] =      @log, 
    [sql path] =      @SQLPath, 
    [Backup Folder] = @Backup

Then I manually go to that folder and have a look at the permissions sql server (the sql server services account) has on that folder.

I would like to script those permissions so that I can apply them to a new folder.

Using powershell, how can I script all those permissions from this folder let's say folder a:\data and apply those permissions to folder b:\data on the same machine?

Answer 1

Get-Acl worked for me to copy permissions from my dev machine DATA folder to a test folder.

$Acl = Get-Acl "C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER01\MSSQL\DATA"
Set-Acl "C:\Program Files\Microsoft SQL Server\test" $Acl

This overwrites the permissions on the new folder, handle with care.

(https://blogs.msdn.microsoft.com/johan/2008/10/01/powershell-editing-permissions-on-a-file-or-folder/)