Petri.com forums Home Forums Start Page Forums Frequently Asked Questions FAQ Member List Members List
Go Back   Petri IT Knowledgebase Forums > Databases > SQL Server Scripting / Automation
Petri.com is happy to award RicklesP the title of Most Valuable Member !!!
Register Calendar Calendar Search Petri IT Knowledgebase Forums Search Todays Posts Today's Posts Mark Forums Read
Notices

Checking Owner of SQL Files

Checking Owner of SQL Files

this thread has 7 replies and has been viewed 11382 times

Closed Thread
 
Thread Tools Search this Thread Display Modes
  #1  
Old 13th October 2010, 17:35
meltondba meltondba is offline
Casual
Casual
 
 Join Date: Jun 2010
  6 month star 12 month star
 Location: Montgomery, AL
 Posts: 7
 Reputation: meltondba is on a distinguished road (10)
Lightbulb 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 at 17:36.. Reason: added tick to last bit of code so it would wrap
  #2  
Old 20th October 2010, 05:29
MidnightDBA MidnightDBA is offline
Moderator
 
 Join Date: May 2010
  6 month star 12 month star
 Posts: 34
 Reputation: MidnightDBA has a spectacular aura aboutMidnightDBA has a spectacular aura aboutMidnightDBA has a spectacular aura about (222)
Default 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
  #3  
Old 20th October 2010, 17:09
meltondba meltondba is offline
Casual
Casual
 
 Join Date: Jun 2010
  6 month star 12 month star
 Location: Montgomery, AL
 Posts: 7
 Reputation: meltondba is on a distinguished road (10)
Default 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?
  #4  
Old 20th October 2010, 18:09
vonPryz vonPryz is offline
Member
Here to help
 
 Join Date: Oct 2008
  6 month star 12 month star
 Location: Finland
 Posts: 354
 Reputation: vonPryz will become famous soon enough (80)
Default Re: Checking Owner of SQL Files

Quote:
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
  #5  
Old 21st October 2010, 20:50
meltondba meltondba is offline
Casual
Casual
 
 Join Date: Jun 2010
  6 month star 12 month star
 Location: Montgomery, AL
 Posts: 7
 Reputation: meltondba is on a distinguished road (10)
Default 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.
  #6  
Old 22nd October 2010, 18:55
vonPryz vonPryz is offline
Member
Here to help
 
 Join Date: Oct 2008
  6 month star 12 month star
 Location: Finland
 Posts: 354
 Reputation: vonPryz will become famous soon enough (80)
Default 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
  #7  
Old 25th October 2010, 22:49
meltondba meltondba is offline
Casual
Casual
 
 Join Date: Jun 2010
  6 month star 12 month star
 Location: Montgomery, AL
 Posts: 7
 Reputation: meltondba is on a distinguished road (10)
Default 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
  #8  
Old 27th October 2010, 16:50
MidnightDBA MidnightDBA is offline
Moderator
 
 Join Date: May 2010
  6 month star 12 month star
 Posts: 34
 Reputation: MidnightDBA has a spectacular aura aboutMidnightDBA has a spectacular aura aboutMidnightDBA has a spectacular aura about (222)
Default 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
Closed Thread


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Calendar event owner phrancie Exchange 2000 / 2003 4 22nd December 2009 23:28
Quotas and owner attribute m80arm Windows Server 2000 / 2003 / 2003 R2 5 3rd May 2009 17:22
Unable to set permissions / new owner on folders/files Lukas SBS 2000 / 2003 3 16th May 2008 15:21
Information on SQL Server files zerotasking Windows Server 2000 / 2003 / 2003 R2 1 17th August 2006 14:57
AD Schema Owner Problem biggles77 Active Directory 11 3rd August 2006 10:11


All times are GMT +3. The time now is 13:16.

Steel Blue 3.5.4 vBulletin Style ©2006 vBEnhanced
Powered by vBulletin® Version 3.8.4
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.
 

Valid XHTML 1.0!   Valid CSS!

Copyright 2005 Daniel Petri