January 2, 2015 at 5:32 pm
Comments posted to this topic are about the item Real World:SQL server configuration Quality check script for DBA's.
January 22, 2015 at 8:37 am
Very interesting. I had something similiar but it isn't as good as this one. Thanks for sharing.
January 22, 2015 at 9:36 am
Very nice little script. Thanks for sharing with the community.
For more security-minded DBAs (which I hope are most of us!) I recommend keeping "show advanced options" turned off, as well as xp_CmdShell. I wound up putting the enabling commands at the end of the script just before step N :
[font="Courier New"]EXEC sp_configure 'show advanced options', 1
RECONFIGURE
EXEC sp_configure 'xp_cmdshell', 1
RECONFIGURE
[/font]
then the "N" display, and then
[font="Courier New"]EXEC sp_configure 'xp_cmdshell', 0
RECONFIGURE
EXEC sp_configure 'show advanced options', 0
RECONFIGURE
[/font]
I'll then keep the entire N block commented out until I specifically want to see its data.
Thanks again!
Mike Hinds Lead Database Administrator1st Source BankMCP, MCTS
January 22, 2015 at 9:36 am
Thanks for the script.
- webrunner
-------------------
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
January 22, 2015 at 10:02 am
A script that purports to "check" settings should NOT change them, period. Even more so without a warning/statement that they've been changed!
If a specific setting needs to be changed to run the script, that should be documented, and a properly written script would check the existing condition first, then set it to match what it was when the script started.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
January 22, 2015 at 10:40 am
Thanks for your support.
February 1, 2015 at 3:13 pm
Thank you for your script, must have taken some time to do. Much appreciated.
I do agree with Mike Hinds and ScottPletcher about changing the settings on xp_cmdshell, although I would warn to also be careful adding Mikes suggestion in and running this on any server as somebody might have set xp_cmdshell for specific reasons and you might just mess things up by going about setting everything back to 0 again.
I therefore spent a bit of time to put together the following to check what the settings are before starting and then setting them back after finishing
---start---
-- Check 'show advanced options' value, write value to variable and set to 1
DECLARE @var_show_advanced_options VARCHAR(1)
SELECT @var_show_advanced_options = CONVERT(VARCHAR(1),value) FROM master.sys.configurations WHERE name = 'show advanced options'
IF @var_show_advanced_options != 1
BEGIN
EXEC sp_configure 'show advanced options', 1
RECONFIGURE
END
-- Check xp_cmdshell value, write value to variable and set to 1
DECLARE @var_xp_cmdshell VARCHAR(1)
SELECT @var_xp_cmdshell = CONVERT(VARCHAR(1),value) FROM master.sys.configurations WHERE name = 'xp_cmdshell'
IF @var_xp_cmdshell != 1
BEGIN
EXEC sp_configure 'xp_cmdshell', 1;
RECONFIGURE;
END
/* Run your XP_CMDSHELL and entire SCRIPT after this line */
/* Run your XP_CMDSHELL and entire SCRIPT before this line */
/* Cleanup */
-- Set xp_cmdshell back to what it was
IF @var_xp_cmdshell != 1
BEGIN
EXEC sp_configure 'xp_cmdshell', @var_xp_cmdshell
RECONFIGURE
END
IF @var_show_advanced_options != 1
BEGIN
EXEC sp_configure 'show advanced options', @var_show_advanced_options
RECONFIGURE
END
---end---
March 29, 2015 at 12:35 pm
I don't think turning off "xp_cmdshell" is anyways a good idea, as without that we can't even run batch commands from sql.
Thanks.
March 29, 2015 at 2:13 pm
Why are we concerned about checking Lock Pages in Memory? Does that help us doing better in DBA activities anymore?
Thanks.
August 31, 2015 at 3:49 am
BEGIN TRY
exec xp_cmdshell @PScmd
END TRY
BEGIN CATCH
BEGIN TRY
exec sp_configure 'xp_cmdshell',1;RECONFIGURE
exec xp_cmdshell @PScmd
exec sp_configure 'xp_cmdshell',0;RECONFIGURE
END TRY
BEGIN CATCH
exec sp_configure 'show advanced options',1;RECONFIGURE
exec sp_configure 'xp_cmdshell',1;RECONFIGURE
exec xp_cmdshell @PScmd
exec sp_configure 'xp_cmdshell',0;RECONFIGURE
exec sp_configure 'show advanced options',0;RECONFIGURE
END CATCH
END CATCH
August 31, 2015 at 7:07 am
Excellent script. Thanks for sharing. I did however run into an issue with Section J: where more than just TEMPDB was returned. It turns out that the logical name for a 3rd party database file was "BlankTemplate". To avoid this issue I changed this line:
DECLARE tempfile_cursor CURSOR FOR SELECT filename from sys.sysaltfiles where name like '%temp%'
To this:
DECLARE tempfile_cursor CURSOR FOR SELECT filename from sys.sysaltfiles where dbid = 2
Thanks again.
Lee
August 31, 2015 at 1:06 pm
Hello all,
You can avoid xp_cmdshell to get max and min memory as follows:
DECLARE
@MaxMemory NVARCHAR(10) -- Max memory
, @MinMemory NVARCHAR(10) -- Min memory
SET @MaxMemory = CONVERT(NVARCHAR(10), (SELECT VALUE FROM SYS.CONFIGURATIONS where Name like 'max server memory%'))
SET @MinMemory = CONVERT(NVARCHAR(10), (SELECT VALUE FROM SYS.CONFIGURATIONS where Name like 'min server memory%'))
PRINT 'Detection of Maximum Memory (Megabytes) --> '+@MaxMemory
PRINT 'Detection of Minimum Memory (Megabytes) --> '+@MinMemory
GO
If your systems require any audit/security compliance then you should never use xp_cmdshell for anything.
Thanks,
Rudy
Rudy
August 31, 2015 at 2:45 pm
Good script, thank you.
September 7, 2015 at 8:43 am
Thanks for sharing Randy!!
You can avoid all those cursors and simplify your code like this:
print 'I. Name of Members in Serveradmin role.:'
DECLARE @PrintResults NVarchar(4000) = ''
SELECT@PrintResults = @PrintResults + ' Login - ' + name COLLATE DATABASE_DEFAULT + CHAR(13)
FROMsys.server_principals
WHEREIS_SRVROLEMEMBER('sysadmin', name) = 1
PRINT@PrintResults
I didn't bother with the second column as it is already stated in the header.
How I deal with configuration options:
DECLARE @AdvOpts Bit
,@CmdShellBit
SELECT@AdvOpts= CAST(MAX(CASE WHEN Name = 'show advanced options' THEN CAST(value_in_use AS TinyInt) ELSE NULL END) AS Bit)
, @Cmdshell = CAST(MAX(CASE WHEN Name = 'xp_cmdshell' THEN CAST(value_in_use AS TinyInt) ELSE NULL END) AS Bit)
FROMsys.Configurations
WHEREName IN ('show advanced options', 'xp_cmdshell')
SELECT[@AdvOpts]= @AdvOpts
, [@CmdShell] = @CmdShell
IF @CmdShell = 0
BEGIN
PRINT 'Warning : server configuration change needed. These will be reverted at the end of the script.'
IF @AdvOpts = 0
BEGIN
EXEC sp_configure 'show advanced options', 1
RECONFIGURE WITH OVERRIDE
END
EXEC sp_configure 'xp_cmdshell', 1
RECONFIGURE WITH OVERRIDE
END
/*
Code
*/
IF @CmdShell = 0
BEGIN
EXEC sp_configure 'xp_cmdshell', 0
RECONFIGURE WITH OVERRIDE
END
IF @AdvOpts = 0
BEGIN
EXEC sp_configure 'show advanced options', 0
RECONFIGURE WITH OVERRIDE
END
February 17, 2016 at 4:50 pm
Interesting, thank you.
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply