Prev: Need help with the Query.
Next: Reference integrity between two databases
From: Mark McGinty on 27 Feb 2010 20:14

"Michael Coles" <admin(a)geocodenet.com> wrote in message
news:25E3DF5E-0098-463F-9B57-C111D1E2C07B(a)microsoft.com...
> "Mark McGinty" <mmcgintyNG(a)sSpUaCmKSdeprecatethis.com> wrote in message
> news:elwuXjrtKHA.4636(a)TK2MSFTNGP06.phx.gbl...
>>
>> "Michael Coles" <admin(a)geocodenet.com> wrote in message
>> news:632EAD0C-C253-43A2-893C-A8B9B6EE5AB4(a)microsoft.com...
>>> That's a system stored proc that's used by client connectivity APIs. If
>>> you disabled permissions or something like that you'd probably have a
>>> hard time connecting to your server using some client APIs (presumably
>>> ODBC in this instance).
>>
>> Umm, it doesn't get called often enough for that to be likely, out of
>> almost 200K rows of trace data, it only gets called 5 times.
>>
>> I thiink I'll look for it in next weeks traces, to make sure it's really
>> an issue; if so I'll abuse a VM to get to the bottom of it.
>
>
> Umm, I'm not following your logic here. Is the line of reasoning "that
> system stored procedure is not called very often so it's not important"?
> If so, you might want to rethink that strategy. There are system stored
> procedures that are called very rarely but are extremely important to the
> proper functioning of your server.
>
> Look, before you do something you're going to regret 30 seconds later you
> might want to do just a tiny bit of research. Try cruising over to this
> page in Books Online before you put your server in an unsupported
> configuration: http://msdn.microsoft.com/en-us/library/ms187961.aspx

I assumed you would be familiar with the abbreviation "VM", but apparently
not -- it stands for "Virtual Machine" the use of which makes it difficult
to regret anything, because anything can be undone with a couple of clicks.


> As I told you this procedure is on the list of system stored procedures.
> It's under the heading "The following stored procedures are not
> documented:".
> That's within the section titled "API System Stored Procedures".
> The first two sentences of that section read:
>
> "Users that run SQL Server Profiler against ADO, OLE DB, and ODBC
> applications may notice these applications using system stored procedures
> that are not covered in the Transact-SQL Reference. These stored
> procedures are used by the Microsoft SQL Server Native Client OLE DB
> Provider and the SQL Server Native Client ODBC driver to implement the
> functionality of a database API."
>
> Then shoot on over to this little article. The article is specifically
> about security, but it mentions what you're considering in the much
> broader sense of all system stored procedures:
> http://www.microsoft.com/technet/prodtechnol/sql/2005/sql2005secbestpract.mspx.
>
> Notice the line that reads:
>
> "Removing the system stored procedures results in an unsupported
> configuration."

Who said anything about removing ANYTHING? My words, "I'm considering
restricting permissions" -- where did you get "remove" out of that?


> So what does this mean for you? Basically it means if you try to disable
> or remove the system stored procedures listed on the first page you
> visited you're going to run into two issues:
>
> (1) You'll probably run into problems connecting to your server with some
> connectivity drivers (the driver you're using obviously needs it; whether
> it's once ever 40,000 rows or once every 1 row is pretty much irrelevant),
> and

By coincidince I happen to have a Virtual Machine (VM) with a cleanly
installed copy of Windows and SQL 2005. (I use it to test theories and
constructs that are outside of the box, you should try it sometime.) I
booted it up, defined an unprivileged user (intuitively named "unpriv") and
I set access denied permissions to sp_table_statistics_rowset and
sp_table_statistics2_rowset -- don't panick, don't get your MVP panties all
up in a wad, all of my mayhem can be easily undone, I know this from
*experience*!

Having made my unsupported changes I proceded to test my ability to connect
to this VM (again, that stands for "Virtuall Machine" in case you're having
difficulty keeping up.) I tested using SSMS, ODBC using the SQL libraries
and Native Client, from script using ADODB (both SQLOLEDB and SQLNCLI) -- I
connected to the VM using practically every way known under the sun, as both
sa and unpriv: NO ISSUES whatsoever! Oh my goodness!

I then applied SP1, SP2, SP3 and an interim patch roll-up -- all completed
without even a whimper, they even left my unsupported permissions intact!
Imagine all of that!

It did cause issues creating linked servers, but I expected that; this was
no side-effect, it was what I had hoped to achieve.

So what does this mean to *you*?

1. Your conclusion "the driver you're using obviously needs it" was
unfounded and ridiculous, and has been proven to be erroneous.
2. "whether it's once ever 40,000 rows or once every 1 row" is very much
relevant, and was likewise a ridiculous statement.
3. None of your definitively stated dire predictions had any basis in fact
or reality, they were all just guesses -- wrong guesses, I might add.
4. You are a horse's ASSet for taking the tone you took in your reply!
Talking down to me, like I'm an idiot, inferring all sorts of risky
intentions not present in anything I said, making all sorts of statements as
if they were factual or based on knowledge, when in the cold harsh light of
testing, all turned out to be WRONG -- don't you ever, EVER talk that way to
me again!

Just because you're an "MVP" that doesn't give you the right to talk down to
people, nor to take that BS tone -- particularly when you haven't even taken
the time to comprehend a given post, and even more particularly when you are
speaking NOT from experience, but from mere supposition.

Did you write your book in the same condescending style? I'm sure it's a
joy to read. I wouldn't pick it up out of a paper recycling bin (a place to
which it's no stranger, I'd be willing to wager) for a nickel. And I
apologize to the trees, on behalf of humanity, for squandering the lives of
their bretheren in such a frivolous and ill-founded manner.

So the next time you go to write a nasty-gram response to a post, you might
want to stick within your sphere of actual knowledge; from my view your
conjecture is very nearly worthless.


Thanks -- for nothing,
MM


(The rest of this I wrote before booting a VM and proving that you were
wrong.)


> (2) You'll put your server into an unsupported configuration. This has
> big implications for the type of support you can expect [think hotfixes,
> service packs, CSS calls, etc.]

Think all of those operations run as a sysadmin, and would not be affected
by what I am considering.


> Since this procedure is on the list of "undocumented" procedures you don't
> know how many drivers depend on it, to what extent, or even what it does.
> Those "unknown-unknowns" tend to be the worst little things to mess around
> with blindly.

I'm not doing anything blindly. I have virtuals as a first line...

And actually, yes I do know what it does, I read the source, it isn't rocket
science. Of course I don't know what depends on it, that was my specific
question, but I'm really not all that interested in your guesses, I was
hoping somebody might actually *know*.


> I'd recommend against it. Microsoft recommends against it. But in the
> end it's your server. Best of luck!

Given the number of flawed assumptions you have made in your reply, I really
couldn't care less what you'd recommend. Haven't you ever heard of limiting
access to system stored procedures? Haven't you ever worked on anything
outside the box? Or do you rely exclusively on whatever you picked-up from
certification exam cram sessions?

btw, don't infer from the number of questionmarks in my reply, that I
actually want an answer from you -- I don't!



> Michael Coles
> SQL Server MVP
> Author, "Expert SQL Server 2008 Encryption"
> (http://www.apress.com/book/view/1430224649)
> ----------------
>


From: Michael Coles on 27 Feb 2010 23:11
"Mark McGinty" <mmcgintyNG(a)sSpUaCmKSdeprecatethis.com> wrote in message
news:%23W5obNBuKHA.2436(a)TK2MSFTNGP04.phx.gbl...
>

Best of Luck! LOL!

--
Thanks

Michael Coles
SQL Server MVP
Author, "Expert SQL Server 2008 Encryption"
(http://www.apress.com/book/view/1430224649)
----------------

First  |  Prev  | 
Pages: 1 2
Prev: Need help with the Query.
Next: Reference integrity between two databases