Announcement

Collapse
No announcement yet.

Checking Owner of SQL Files

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Checking Owner of SQL Files

    I made a mistake by not coming here first so hopefully someone can help on this one...

    For anyone that works with the military or government agency they have to deal with DoD STIGs (Security Technicial Implementation Guides). These are pretty much just a security hardening of SQL Server installations. One of the checks in these checklist for SQL Server is to check the ownership of the DBMS application and configuration files. They want to ensure that the account that installed or is running SQL Server owns the files. So this is pretty much the binary files and the directories that are created for an SQL Server instance.

    I want to use PowerShell to do this, cause I know it can. I have gotten stuck. This is what I have so far...

    This gives me the path and filename that I need in order to use the cmdlet Get-Acl to get the Owner of each file:
    Code:
     
    $path = 'T:\MSSQL'
    dir $path -Recurse | ft FullName
    This will get me the owner of a file:
    Code:
     
    (Get-Acl 'T:\MSSQL\log\ERRORLOG').Owner
    I though I could combine that into this:
    Code:
     
    dir $path -Recurse | ft FullName | ForEach-Object {(Get-Acl $_).Owner}
    Then I could also use this to get the permissions for the files:
    Code:
     
    dir $path -Recurse | ft FullName | ForEachObject {(Get-Acl $_).Access | `
    ft FileSystemRights, AccessControlType, IdentityReference -AutoSize}
    However, I get caught in that Get-Acl cmdlet only accepts input of System.String. and "ft FullName" is not passing the object as a string. So my question(s) are:
    1) Is this the proper way to do it?
    2) How do I convert the object into the ForEach-Object cmdlet to be a String path?
    Last edited by meltondba; 13th October 2010, 15:36. Reason: added tick to last bit of code so it would wrap

  • #2
    Re: Checking Owner of SQL Files

    Hey dude, sorry it took me so long to get to this, but I just saw it. Actually, you don't need the FT in there in the middle of your cmd. It's not perfect, but I took a couple mins to sketch out something that'll at least get you the info you need.
    You should be able to modify it anyway you like...

    > dir . | ?{$ACL = (get-acl "$_").owner; "$($_.FullName) Owner: $ACL" | out-file f:\bcp
    test\ACL.txt -append }

    Let me know if you need something more specific.
    Sean McCown, SQL Server MVP

    See my FREE SQL Server training videos at:
    http://www.MidnightDBA.com

    Blog Author of:
    Database Underground -- http://infoworld.com/blogs/sean-mccown
    DBA Rant http://dbarant.blogspot.com

    Comment


    • #3
      Re: Checking Owner of SQL Files

      Thanks, that gets me to more of what I want.

      Although I'm curious about this portion: "$($_.FullName)". I know the $_ is the object passed through the pipe, what does the $ outside the () do?

      Comment


      • #4
        Re: Checking Owner of SQL Files

        Originally posted by meltondba View Post
        Thanks, I know the $_ is the object passed through the pipe, what does the $ outside the () do?
        The syntax is used to evaluate the $_.FullName so that it will print the current item's full name.

        Try running the script like so,
        Code:
        dir . | ?{$ACL = (get-acl "$_").owner; write-host "($_.FullName) Owner: $ACL" }
        What you'll get is something like this:

        Code:
        (Desktop.FullName) Owner: DOMAIN\user
        (Favorites.FullName) Owner: DOMAIN\user
        (My Documents.FullName) Owner: DOMAIN\user
        Add the $ and the output will contain actual paths for the items.
        -vP

        Comment


        • #5
          Re: Checking Owner of SQL Files

          Now the next step is how to do I add in -Recurse on the dir statement and then be able to pass that through the where-object?

          The probelm I'm hitting is that when you add -recurse it only passes the name of the file or subfolder and not the full path as the Get-Acl or needs.

          Comment


          • #6
            Re: Checking Owner of SQL Files

            When I need to know how to solve this kind of problems, my approach is to divide and conquer. First step is to find what kind of members gci will return. So let's find out:
            Code:
            $d = gci
            $d[0]|gm
            What I've done is to take a get-childitem listing and put it to $d. Then I access the first thing and ask its members.

            Now, there are lots of properties and methods. This one looks promising:
            Code:
            PSPath    NoteProperty    System.String    PSPath=Microsoft.PowerShell.Core\FileSystem::C:\Documents and...
            So let's take a peek at it:
            Code:
            $d[0].pspath
            Microsoft.PowerShell.Core\FileSystem::C:\Documents and Settings\vonPryz\scripts\cmd
            Sure enough, that's what I am looking for. If, on the other hand, there wouldn't be any sensible properties, I'd dig up the .Net class and look its documentation from MSDN. To get the object type, use .gettype():
            Code:
            $d[0].gettype()
            IsPublic IsSerial Name              BaseType
            True     True     DirectoryInfo     System.IO.FileSystemInfo
            To sum up, add property access to the current object on the pipeline, like so:

            Code:
            gci -recurse | % {
              $ACL = $(get-acl $_.pspath).owner; 
              write-host "$($_.FullName) Owner: $ACL";
            }
            -vP

            Comment


            • #7
              Re: Checking Owner of SQL Files

              Much appreciated folks.

              I recall looking at the get-member but just don't mess with it enough to know what I'm looking at yet

              Comment


              • #8
                Re: Checking Owner of SQL Files

                Hey Melton,
                The $($_.FullName) construct is what I think is called a temp parameter in PS. Don't quote me on the name, but it allows you to get around the problem of the data not showing up correctly. It's the "." that messes things up. If you were to type the line like this:
                $_.FullName
                what you would get would be something like this:
                C:\MyFile.FullName

                That's because the period messes things up and tells PS to process it differently. So you have to wrap the whole thing in () and make a temp var out of it...
                That tells PS to fully expand the inner var and give you the value.
                It's really a handy skill to know because it pops up everywhere.
                Sean McCown, SQL Server MVP

                See my FREE SQL Server training videos at:
                http://www.MidnightDBA.com

                Blog Author of:
                Database Underground -- http://infoworld.com/blogs/sean-mccown
                DBA Rant http://dbarant.blogspot.com

                Comment

                Working...
                X