Global Configuration Settings

Question

Global Configuration Settings

Viewing 15 posts - 16 through 30 (of 35 total)

You must be logged in to reply to this topic. Login to reply

TomThomson SSC Guru Points: 104773 More actions · Answer 1

Sean Smith-776614 (8/29/2013)
Lesson is use the complete name. For example, rather than using "advanced options" use "show advanced options". If MS ever introduces "hide advanced options" then you may need to rewrite some code. Essentially, just be aware of how it works. Just an FYI. 🙂
Just curious, did you get it right or wrong?

Why would the server think that 'ow a' was a substring of 'hide advanced options'? :hehe:

Tom

TomThomson SSC Guru Points: 104773 More actions · Answer 2

An nice amusing question that demonstrates an unfortunate feature of SQL - using contractions is not a habit I would like to get into. so a good question about something that people may be unaware of. I hope people don't take away the lesson that this is a good thing to use, though.

However, the correct answer is wrong - the real answer is "it depends". Since security is a useful thing to have, I normally log into servers as a non-member of local administrators; if I then connect to SQL Server, I don't get SA privileges, because local admins (for machines not in a domain) or domain or enterprise admins (for machines in a domain) get SA privileges.

So whether the first call works or not depends on whether I remembered to log in as an admin user because I was going to try to switch xp_cmdshell on, or (more likely) remembered to leave my current unprivileged SSMS and start it up again as an admin user.

Of course the use of tick boxes indicates that more than one option is to be chosen, which gives the game away as only one of the three options could be true if I had connected without the required privileges.

Tom

Jeff.MSSqlSage Say Hey Kid Points: 709 More actions · Answer 3

L' Eomot Inversé (8/30/2013)
An nice amusing question that demonstrates an unfortunate feature of SQL - using contractions is not a habit I would like to get into. so a good question about something that people may be unaware of. I hope people don't take away the lesson that this is a good thing to use, though.
However, the correct answer is wrong - the real answer is "it depends". Since security is a useful thing to have, I normally log into servers as a non-member of local administrators; if I then connect to SQL Server, I don't get SA privileges, because local admins (for machines not in a domain) or domain or enterprise admins (for machines in a domain) get SA privileges.
So whether the first call works or not depends on whether I remembered to log in as an admin user because I was going to try to switch xp_cmdshell on, or (more likely) remembered to leave my current unprivileged SSMS and start it up again as an admin user.
Of course the use of tick boxes indicates that more than one option is to be chosen, which gives the game away as only one of the three options could be true if I had connected without the required privileges.

I think you can always make this argument, though.

"What if the SQL Server had caught fire moments earlier ? Then none of the commands would work."

Sean Smith SSCertifiable Points: 5942 More actions · Answer 4

Thanks for all the feedback everyone. Again, just more of an FYI about the behavior of the sp. It just caught me off gaurd when I realized it worked this way and all I thought was "Wow, this is a BAD design." :p

Mike Hays SSCommitted Points: 1871 More actions · Answer 5

Mike Hays

SSCommitted

Points: 1871

August 30, 2013 at 7:35 am

#1646361

Well, I learned something. Thanks!

doug.davidson SSCommitted Points: 1662 More actions · Answer 6

Depending on the configuration setting of SQL you could have the option of both statements failing.

Unless the reconfigure statement was followed by "with override"

Reconfigure with Override

george sibbald SSC Guru Points: 104210 More actions · Answer 7

of course, if your aim is to save typing, rather than

EXECUTE sys.sp_configure

@configname = 'show advanced options'

,@configvalue = 1

type

exec sp_configure 'show advanced options',1

🙂

other than that the question highlights an interesting tid-bit of information.

---------------------------------------------------------------------

webrunner SSC-Dedicated Points: 31718 More actions · Answer 8

The question has 'dsh' and the explanation has 'dhs' - which is it?

-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html

webrunner SSC-Dedicated Points: 31718 More actions · Answer 9

webrunner (9/3/2013)
The question has 'dsh' and the explanation has 'dhs' - which is it?

Never mind. It is 'dsh' as in 'commandshell'.

🙂

-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html

SQLRNNR SSC Guru Points: 281334 More actions · Answer 10

alex.d.garland (8/30/2013)
"The SQL Server Database Engine recognizes any unique string that is part of the configuration name."
Wow, I did not know that. Good question, I always like it when I learn something new.

+1

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events