Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««12345»»

What is your favorite "I didn't know that" moment in T-SQL? Expand / Collapse
Author
Message
Posted Friday, August 9, 2013 3:37 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 3:47 AM
Points: 7,855, Visits: 9,603
Koen Verbeeck (8/8/2013)
Sean Lange (8/8/2013)
This whole English thing is the reason we American's don't know any other languages. All the others have actual rules that you follow all of the time. In English our rules are more like guidelines. We follow them most of the time, except for all the exceptions. It takes more than a lifetime to master such a complex and loose set of rules. We just don't have time to learn another language, and we would hate to realize that the other languages make far more sense than our own.


Not all languages make sense. In Dutch we say some of the numbers backwards. For example, we say 62 as 'twee en zestig', which translates to 'two and sixty'.
Just to confuse the hell out of everyone trying to learn our language
And especially useful when you are dictating a phone number or house number over the phone...

You think "twee en zestig" is bad? Just try "dhà fhear air tri fichead" ("two man after three score" for "62 men") which (apart from spelling changes between the two) is what the Gaels in Scotland and Ireland put up with. I think the idea of putting the number all in one place instead of sticking the thing being counted in the middle of the number is a brilliant invention, and you Dutch have had it for ages while it's only been allowed in Gàidhlig for a couple of decades (and is nowhere near universal yet). And our dual number has merged with the singular instead of the plural (and a number like 62 counts as 2, for those purposes).


Tom
Post #1482963
Posted Friday, August 9, 2013 3:46 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 3:47 AM
Points: 7,855, Visits: 9,603
Jeff Moden (8/8/2013)
Koen Verbeeck (8/8/2013)
Sean Lange (8/8/2013)
Koen Verbeeck (8/8/2013)
Jeff Moden (8/8/2013)
It doesn't? I just called the article up and here's the Prologue that I "remembered" adding to the article.


Hmmm, this might be the language gap. I was being affirmative.
You say: "it doesn't get better ...", I say: "yes, it doesn't". Doesn't it work that way?


Koen the way you said it seems like it would be correct but yet again English has to be a bit strange. We would say "No, it doesn't".


English: sense it makes none.


Must be because I obviously took it as the negative. BWAAA-HAAA!!!! No wonder people of different languages go to war over dumb things... they were saying the same thing, took it the wrong way, and decided to fight about it instead of talk about it. There are even "language gaps" right here in the U.S.A. In the mid west, if someone were to say "I have a silver colored truck", I would respond, "So do I" because I also have a silver colored truck. In Rhode Island (part of the north east or "New England" states), someone else would say "So don't I" if they also have a silver colored truck and I just don't get that.


That of course is exactly the same inversion of sense that many Americans use when they convert standard English "I couldn't care less" to (majority American, not universal though) "I could care less". I would take it as meaning the opposite of what it apparently does - and now that you've warned me I may understand it if I ever come across it (but I've no plans to go to RI, so I probably will never hear it).


Tom
Post #1482967
Posted Friday, August 9, 2013 4:08 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 3:47 AM
Points: 7,855, Visits: 9,603
ChrisM@Work (8/9/2013)
Sean Lange (8/8/2013)
This whole English thing is the reason we American's don't know any other languages. All the others have actual rules that you follow all of the time. In English our rules are more like guidelines. We follow them most of the time, except for all the exceptions. It takes more than a lifetime to master such a complex and loose set of rules. We just don't have time to learn another language, and we would hate to realize that the other languages make far more sense than our own.


It's not yours - it's ours! We don't even charge rent!

Nonsense; those who live on the west side of the pond speak a language quite distinct from that of the Anglophone British (although not quite as different as it is from the languages of the other British). This was pointed out quite clearly by Oscar Wilde (an Irishman) in 1887, approximately 55 years before various historians of English literature claim it was first notice by Bernard Shaw (who of course was another Irishman, although the English chauvinist journalists who wanted to award him responsibility for that discovery were probably not aware of that - they'd surely have picked someone else if that had been).

Thinking of that has reminded me of Shaw, and almost I'm tempted to change my sig. But I don't think that the Shaw quotation "I have defined the hundred per cent American as ninety-nine per cent an idiot" would go down too well as a sig at all, because the very thing that would make it an amusing quote (that most people would take it as applying to all Americans, instead of just to one particular person) would mean that most people would take offence at it.


Tom
Post #1482972
Posted Friday, August 9, 2013 4:42 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 3:47 AM
Points: 7,855, Visits: 9,603
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.


Tom
Post #1482974
Posted Friday, August 9, 2013 8:13 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 11:32 PM
Points: 35,584, Visits: 32,174
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1482991
Posted Saturday, August 10, 2013 1:27 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 3:47 AM
Points: 7,855, Visits: 9,603
Jeff Moden (8/9/2013)
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.

The big problem in SQL Server 2000 back in 2002 or thereabouts was that specifying a non-privileged proxy account for xp_cmdshell didn't work (or at least I never managed to get it to work, and MS told me it wasn't supposed to work yet; a bit later - sp3 - they claimed it now worked, but it still didn't: calling xp_sqlagent_proxy_account now changed SqlServerAgent's login, but not the xp_cmdshell login for non-SA users). That meant that if you allowed users who were not system administrators to use it they could run code using all the privileges that the account running SQL Server had; if the proxy account had been workable, such users would have had access only to whatever privileges the proxy account had, but it wsn't. So making xp_cmdshell available to everyone, instead of just to those with SA privileges, meant that everybody could now do as much damage to the platform (Windows 2000 Server or, a year oo two later, Windows 2003Windows 2003 Server) as people with SA privileges could. The security problem with xp_cmdshell wasn't anything to do with the specification, only with the bug that meant you couldn't use it as it was intended to be used because it didn't work correctly. Disabling it for non-SA users did deliver improved security. To make matters worse - impossible to give xp_cmdshell safely to ordinary users whatever you did - (by design, which must be one of the nuttiest design decisions ever made) impossible to make it available to non-SA users in the case when SQL Server was using an NT account that wasn't a member of the local administrators group, so you couldn't get round the problem and give non-SA users safe access to xp_cmdshell by running under a non-admin account.
Yes, if people can get SA access they can do damage; but in SQL Server 2000 if people without SA access had access to xp_cmdshell they could do anything with it that someone with SA privilege could do.


Tom
Post #1483056
Posted Friday, September 27, 2013 6:18 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 11:32 PM
Points: 35,584, Visits: 32,174
Apologies for the very late reply, Tom. Thanks for the detailed explanation about xp_CmdShell in SQl Server 2000. I knew a good amount of that but it was a great reminder for something... a lot of myths in SQL Server were once fact in previous versions. People were absolutely correct in having an almost visceral fear about xp_CmdShell in SQL Server 2000 and earlier. It's much more as of SQL Server 2005 but people still have that learned fear and have carried it forward.

I will echo one visceral fear about it, though. It's still bloody foolish to give a non-SA user the proxy privs to run it directly for many of the reasons you mentioned as to why it was a bad idea in 2000 or earlier. It is, however, super easy to give a user privs to execute a stored procedure that uses xp_CmdShell in a highly controlled manner without that user being able to use xp_CmdShell directly.

Again, thank you for the thoughtful reply and the subliminal reminder about why certain types of SQL Myths are propagated. It's been a big help.


--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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1499623
Posted Tuesday, October 8, 2013 12:23 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: 2 days ago @ 4:53 AM
Points: 3,422, Visits: 5,368
Jeff Moden (8/8/2013)
Sean Lange (8/8/2013)
I can still remember one of those Eureka moments. I was reading a post from Wayne Sheffield and he explained that in order to understand set programming in sql you need to "stop thinking about what you want to do to a row and instead think about what you want to do to a column". Not sure I got the quote exactly right but for whatever reason that flipped a switch in my brain and it all became clear.


I believe Mr. Sheffield was quoting me in that post. Please see my signature line.


Good to confirm the origin of that quote when you try to live by it.



My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.


Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1502466
Posted Tuesday, October 8, 2013 12:26 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: 2 days ago @ 4:53 AM
Points: 3,422, Visits: 5,368
At the risk of bringing this thread back on track, I'd have to say that without the slightest doubt my greatest SQL Eureka moment was hitting on the CROSS APPLY VALUES approach to UNPIVOT, leading me to the article that's the first in my signature links.


My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.


Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1502468
Posted Sunday, October 13, 2013 11:34 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Monday, November 3, 2014 4:30 PM
Points: 4,574, Visits: 8,366
L' Eomot Inversé (8/10/2013)
but in SQL Server 2000 if people without SA access had access to xp_cmdshell


How?
Post #1504319
« Prev Topic | Next Topic »

Add to briefcase «««12345»»

Permissions Expand / Collapse