• opc.three (4/23/2013)


    Jeff Moden (4/23/2013)


    opc.three (4/23/2013)


    If you ever find yourself needing to implement functionality that crosses the OS/SQL Server gap then PowerShell can fill that need quite nicely for you. PowerShell is also very good in a distributed environment where we need to communicate with and manage multiple instances that may not be directly reachable from one another rendering tools like Linked Servers and xp_cmdshell less than useful.

    You could use tools like xp_cmdshell, the OLE Automation Procedures, Linked Servers, or other functionality built into T-SQL but you may find that in a security-conscious environment tools that access the file system or network from within T-SQL may not be sanctioned for use or will simply not be able to get the job done.

    Gosh... sorry to be an itch but there's nothing about PowerShell that's any more secure than the other methods. There's no natural logging and there's certainly no natural traces on who accessed what or who deleted what using PowerShell. Yes, I absolutely agree that it's a wonderful tool... but not because someone is security conscious because, like the other tools, it offers no natural security.

    There is no need to apologize Jeff. I know where you're coming from, but once again I must call you out on the falsehood of one of your many arguments in this area. Employing PowerShell for some tasks does in fact bring with it far fewer barriers in the areas of auditing and securing an environment than do some of the T-SQL methods. Now I'll apologize...sorry, but there really is no denying that fact 😉

    Correct me if I'm wrong... If you're sitting at a command prompt and you delete files using either DOS or Powershell, there will be no difference. To log the deletes, you have to turn something else on to log it. PowerShell has no advantage there. It also doesn't trace you activity any more than running a batch file would unless you have some other tool running.

    Yep... I agree that if you do it through an SQL Server Job, SSIS, or xp_CmdShell, the only logging it may do is to identify the server/service that did the deletes. No arguments there. But just using PowerShell over something else is no real security enhancement. It's "just" another shell.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)