1

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.

enter image description here

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?

2

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/)

|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.