What is your favorite "I didn't know that" moment in T-SQL?
-
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. :hehe: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
-
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
-
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. :hehe: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
-
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
-
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) -
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
-
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.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) -
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![/I]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?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St -
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![/I]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?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St -
L' Eomot Inversé (8/10/2013)
but in SQL Server 2000 if people without SA access had access to xp_cmdshellHow?
_____________
Code for TallyGenerator -
What is your favorite "I didn't know that"...
Where to start?
Drag and drop columns from Object Explorer in Query Analyzer?
Declaring column names in derived tables in a way later used in CTE?
FROM (select 1, 2
) DT (ID, Value)
Running totals trick with UPDATE using accumulating variables?
I'm sure I'll remember more when I'm offline... 😉
_____________
Code for TallyGenerator -
Amongst others and in no particular order
Numbers/tally table
Calendar tables
CROSS APPLY for PIVOTing
Gap and islands via ROW_NUMBER
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
-
Sergiy (10/13/2013)
L' Eomot Inversé (8/10/2013)
but in SQL Server 2000 if people without SA access had access to xp_cmdshellHow?
I can't speak for Tom but apparently through methods similar to what they are today but with the possible fault that Tom speaks of. Here's a copy of what BOL 2000 sp3 has to say on the subject. I can't confirm the behaviour that Tom was speaking of because I no longer have SQL Server 2000 loaded anywhere.
By default, only members of the sysadmin fixed server role can execute this extended stored procedure. You may, however, grant other users permission to execute this stored procedure.
When xp_cmdshell is invoked by a user who is a member of the sysadmin fixed server role, xp_cmdshell will be executed under the security context in which the SQL Server service is running. When the user is not a member of the sysadmin group, xp_cmdshell will impersonate the SQL Server Agent proxy account, which is specified using xp_sqlagent_proxy_account. If the proxy account is not available, xp_cmdshell will fail. This is true only for Microsoft® Windows NT® 4.0 and Windows 2000. On Windows 9.x, there is no impersonation and xp_cmdshell is always executed under the security context of the Windows 9.x user who started SQL Server.
Note In earlier versions, a user who was granted execute permissions for xp_cmdshell ran the command in the context of the MSSQLServer service's user account. SQL Server could be configured (through a configuration option) so that users who did not have sa access to SQL Server could run xp_cmdshell in the context of the SQLExecutiveCmdExec Windows NT account. In SQL Server 7.0, the account is called SQLAgentCmdExec. Users who are not members of the sysadmin fixed server role now run commands in the context of this account without specifying a configuration change.
Permissions
Execute permissions for xp_cmdshell default to members of the sysadmin fixed server role, but can be granted to other users.
To summarize my opinion about all of that, it's still bloody foolish to grant non-SA prived users the privs to run xp_CmdShell directly. It's ok to give them privs to execute a stored procedure (that they can't change) that uses it or, maybe, through a job that they can start but not change but they should never be given privs to use it directly (that also means that no application login has SA privs). Otherwise, xp_CmdShell is safe to use by trusted SA's, in stored procedures, and in jobs.
--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)
Viewing 13 posts - 31 through 42 (of 42 total)
You must be logged in to reply to this topic. Login to reply