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) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
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)
FROM sys.server_principals
WHERE IS_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
,@CmdShell Bit
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)
FROM sys.Configurations
WHERE Name 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