Home Forums SQL Server 7,2000 T-SQL What is your favorite "I didn't know that" moment in T-SQL? RE: What is your favorite "I didn't know that" moment in T-SQL?

  • L' Eomot Inversé (8/9/2013)


    Eureka moment (I'm getting on topic after having responded to far too many off topic messages above).

    Back in 2002 (I think; it may have been later) I discovered that I didn't have to write JobSteps in T-SQL; I could use ActiveScript and CMDEXEC as well as T-SQL (there were a pile of other sorts of step I could use too, but those were of far less interest). I had been resigned to making XP_CMDSHELL available so that (a) we could execute a text string which invoked cscript and (b) we could do things like deleting files. Suddenly I realised we could run ActiveScript without using XP_CMDSHELL, and use CMDEXEC for trivia like file deletion. This turned the world upside down - now many things became possible that were not before (or were not without screwing up security, because proxy account operation in SQL 2000 at whatever SP was then available was very flawed).

    So now we could use the really excellent scheduling capability of SQL Agent to handle the automation of maintenance, problem diagnosis, and error recovery on all our customers' servers instead of trying to do things with the awful scheduling provided by Windows/DOS, and let SQL agent tie job steps together for us rather than writing C++ to do the scheduling.

    That's another article I'm currently shaking out. Disabling xp_CmdShell doesn't really do a thing to help security. Worse yet, having it disabled may make people lazy about the very security issues that would allow people to turn it on. And, as you've seen, if someone get's in as SA that isn't supposed to, your server is their oyster. They can use xp_CmdShell, create self deleting attack jobs, etc, etc, etc.

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