locked
Powershell script help (query db, get Acl and insert db) RRS feed

  • Question

  • Hello all,

    Trying to implement a script that will do the follow.

    Will divide doubts in parts

    Part1 - Query database and get the desired data

    Query table1 in database teste for ID and FULL PATH.

    ID

    FULL_PATH

    1

    C:\test

    2

    C:\test\1

    3

    C:\test\2

     

    Part2 - For each entry get the ACL info

    Part 3 - Insert info on database table.

    And for each line will get the the ACLS of full path and insert all in table2

    ID

    Group

    Access

    1

    domain\Domain Users

    Administrators Allow  FullControl

    NT AUTHORITY\SYSTEM Allow  FullControl

    BUILTIN\Users Allow  ReadAndExecute,

    2

    domain\Domain Users

     

    3

    domain\Domain Users

     

     

    Does any1 have done something similar. Can any1 help me with this or point me in the right direction?

     

    Regards,


    Tuesday, June 2, 2015 10:46 AM

Answers

  • (get-acl $path).Access|ft -auto


    \_(ツ)_/

    • Proposed as answer by AnnaWY Wednesday, June 17, 2015 8:07 AM
    • Marked as answer by AnnaWY Tuesday, June 23, 2015 1:46 PM
    Tuesday, June 2, 2015 1:27 PM
  • Hi Arestas,

    In addition, you can also refer to the script below to add the folder path in the output and export the access right as string:

    $result = foreach ( $path in  Invoke-Sqlcmd -Query "select ID,Full_PATH from Folders" -ServerInstance INST01 -Database Fileserver -Username user -Password pass)
     {
     $p = $path.Full_PATH
     #Test-Path $path.Full_PATH
     Get-Acl $p |select AccessToString,@{N="path";E={$p}}
     } 
    
    $result|fl
    #export to csv file
    $result|Export-Csv d:\test1\sql.csv

    If we can export the ACL data as csv file, then you can refer to the script below to import csv file to sql server:

    Four Easy Ways to Import CSV Files to SQL Server with PowerShell

    If there is anything else regarding this issue, please feel free to post back.

    Best Regards,

    Anna Wang


    Please remember to mark the replies as answers if they help and unmark them if they provide no help. If you have feedback for TechNet Support, contact tnmff@microsoft.com

    • Proposed as answer by AnnaWY Wednesday, June 17, 2015 8:07 AM
    • Marked as answer by AnnaWY Tuesday, June 23, 2015 1:46 PM
    Thursday, June 4, 2015 2:28 AM

All replies

  • Start by posting your script and asking a specific question or post any errors you are getting.

    We will not write this script for you.


    \_(ツ)_/

    Tuesday, June 2, 2015 1:15 PM
  • Ok,

    Iam still working with my script.

    Part1 is is done:

    Invoke-Sqlcmd -Query "select ID,Full_PATH from Folders" -ServerInstance INST01 -Database Fileserver -Username user -Password pass

    It returns the info i want.

    Now will start with part2 that is, for each returned line get the desired ACLs. Still working around with it and doesnt looks simple.

    Regards

    Tuesday, June 2, 2015 1:23 PM
  • (get-acl $path).Access|ft -auto


    \_(ツ)_/

    • Proposed as answer by AnnaWY Wednesday, June 17, 2015 8:07 AM
    • Marked as answer by AnnaWY Tuesday, June 23, 2015 1:46 PM
    Tuesday, June 2, 2015 1:27 PM
  • Tks for you input.

    Part 2 also done

    foreach ( $path in Invoke-Sqlcmd -Query "select ID,Full_PATH from Folders" -ServerInstance INST01 -Database Fileserver -Username user -Password pass)
    {
    $path.Full_PATH
    #Test-Path $path.Full_PATH
    $acl = (Get-Acl $path.Full_PATH).Access|ft -AutoSize
    echo $acl
    }

    Output:

    ########################3

    C:\inventory\Components21\

               FileSystemRights AccessControlType IdentityReference                IsInherited InheritanceFlag
                                                                                                             s
               ---------------- ----------------- -----------------                ----------- ---------------
    ReadAndExecute, Synchronize             Allow NT AUTHORITY\Authenticated Users        True ...bjectInherit
                    FullControl             Allow BUILTIN\Administrators                  True ...bjectInherit
                    FullControl             Allow NT AUTHORITY\SYSTEM                     True ...bjectInherit

    #######################

    Now time to go to part3.

    Insert on database.


    Tuesday, June 2, 2015 1:57 PM
  • Hi Arestas,

    In addition, you can also refer to the script below to add the folder path in the output and export the access right as string:

    $result = foreach ( $path in  Invoke-Sqlcmd -Query "select ID,Full_PATH from Folders" -ServerInstance INST01 -Database Fileserver -Username user -Password pass)
     {
     $p = $path.Full_PATH
     #Test-Path $path.Full_PATH
     Get-Acl $p |select AccessToString,@{N="path";E={$p}}
     } 
    
    $result|fl
    #export to csv file
    $result|Export-Csv d:\test1\sql.csv

    If we can export the ACL data as csv file, then you can refer to the script below to import csv file to sql server:

    Four Easy Ways to Import CSV Files to SQL Server with PowerShell

    If there is anything else regarding this issue, please feel free to post back.

    Best Regards,

    Anna Wang


    Please remember to mark the replies as answers if they help and unmark them if they provide no help. If you have feedback for TechNet Support, contact tnmff@microsoft.com

    • Proposed as answer by AnnaWY Wednesday, June 17, 2015 8:07 AM
    • Marked as answer by AnnaWY Tuesday, June 23, 2015 1:46 PM
    Thursday, June 4, 2015 2:28 AM
  • Hello all,

    Trying to implement a script that will do the follow.

    Will divide doubts in parts

    Part1 - Query database and get the desired data

    Query table1 in database teste for ID and FULL PATH.

    ID

    FULL_PATH

    1

    C:\test

    2

    C:\test\1

    3

    C:\test\2

     

    Part2 - For each entry get the ACL info

    Part 3 - Insert info on database table.

    And for each line will get the the ACLS of full path and insert all in table2

    ID

    Group

    Access

    1

    domain\Domain Users

    Administrators Allow  FullControl

    NT AUTHORITY\SYSTEM Allow  FullControl

    BUILTIN\Users Allow  ReadAndExecute,

    2

    domain\Domain Users

     

    3

    domain\Domain Users

     

     

    Does any1 have done something similar. Can any1 help me with this or point me in the right direction?

     

    Regards,


    Hello Arestas,

    I'm looking for the exact same functionality. Did you succeed in finding of creating a script that works?

    Kind regards,

    Frans Erich

    Monday, January 27, 2020 10:36 AM