MS SQL Server 2008: Script zur Evaluierung von Server-Eigenschaften

--evaluateSQL2008.sql
--ermittelt Härtungseinstellungen für SQL Server 2008 Systeme


set nocount on

--ermitteln ob SQL 2008 Instanz
declare @regkey nvarchar(500)
declare @InstanzName nvarchar(255)
declare @InstanzVersion nvarchar(50)
IF @@SERVERNAME like ('%\%')-- benannte Instanz wenn \ im Servername
set @InstanzName=SUBSTRING(@@servername,(Charindex('\',@@servername))+1,LEN(@@servername))
else set @instanzName='MSSQLSERVER' --Standardinstanz
select @InstanzVersion=substring(@@VERSION,0,47)
IF @InstanzVersion like ('%2008%')
--wenn sql server 2008
goto Evaluation
--wenn nicht
else
Goto Abbruch

Evaluation:
Print 'Ermittelte SQL Version: '+@InstanzVersion
--Beginn der Evaluierung
PRINT 'Beginn der Evaluierung'
--evaluiertes System
PRINT 'evaluierte Instanz: '+@@servername
--Evaluierungszeitpunkt
PRINT 'Evaluierungszeitpunkt: '+CONVERT(nchar(50),getdate())
PRINT''


--Ermitteln der SQL Dienste-Startart
PRINT 'Ermitteln der Startart von MS SQL Diensten:'
PRINT ''

--SQL Server Engine
create table #services(Value nvarchar(5),Data int)
insert into #services execute master.dbo.xp_regread 'HKEY_LOCAL_MACHINE'
, 'SYSTEM\CurrentControlSet\Services\MSSQLSERVER', 'Start'
SELECT  'SQL Server Dienst : ',Startart=
      CASE Data
         WHEN 2 THEN 'Automatisch'
         WHEN 3 THEN 'Manuell'
         WHEN 4 THEN 'Deaktiviert'
      END
FROM #services
truncate table #services

--SQL Server Agent
insert into #services execute master.dbo.xp_regread 'HKEY_LOCAL_MACHINE'
, 'SYSTEM\CurrentControlSet\Services\SQLSERVERAGENT', 'Start'
SELECT  'SQL Server Agent  : ',--Startart=
      CASE Data
         WHEN 2 THEN 'Automatisch'
         WHEN 3 THEN 'Manuell'
         WHEN 4 THEN 'Deaktiviert'
      END
FROM #services
truncate table #services

--MSDTC
insert into #services execute master.dbo.xp_regread 'HKEY_LOCAL_MACHINE'
, 'SYSTEM\CurrentControlSet\Services\MSDTC', 'Start'
SELECT  'MSDTC             : ',--Startart=
      CASE Data
         WHEN 2 THEN 'Automatisch'
         WHEN 3 THEN 'Manuell'
         WHEN 4 THEN 'Deaktiviert'
      END
FROM #services
truncate table #services

--SQL Server Analysis Services (MSSQLSERVER):
insert into #services execute master.dbo.xp_regread 'HKEY_LOCAL_MACHINE'
, 'SYSTEM\CurrentControlSet\Services\MSSQLServerOLAPService', 'Start'
SELECT  'Analysis Services : ',--Startart=
      CASE Data
         WHEN 2 THEN 'Automatisch'
         WHEN 3 THEN 'Manuell'
         WHEN 4 THEN 'Deaktiviert'
      END
FROM #services
truncate table #services

--SQL Server Reporting Services (MSSQLSERVER):
insert into #services execute master.dbo.xp_regread 'HKEY_LOCAL_MACHINE'
, 'SYSTEM\CurrentControlSet\Services\ReportServer', 'Start'
SELECT  'Reporting Services: ',--Startart=
      CASE Data
         WHEN 2 THEN 'Automatisch'
         WHEN 3 THEN 'Manuell'
         WHEN 4 THEN 'Deaktiviert'
      END
FROM #services
truncate table #services

--SQL Server Browser:
insert into #services execute master.dbo.xp_regread 'HKEY_LOCAL_MACHINE'
, 'SYSTEM\CurrentControlSet\Services\SQLBrowser', 'Start'
SELECT  'SQL Server Browser: ',--Startart=
      CASE Data
         WHEN 2 THEN 'Automatisch'
         WHEN 3 THEN 'Manuell'
         WHEN 4 THEN 'Deaktiviert'
               END
FROM #services
PRINT ''
PRINT ''
truncate table #services
--temporäre Tabelle #services löschen
drop table #services

--Ermitteln von HTTP Endpunkten
create table #httpEndpoints(Name nchar(25),Protokoll nchar(4),Status nchar(15))
PRINT 'Ermitteln von HTTP Endpunkten:'
PRINT''
IF  EXISTS (select name,protocol_desc,state_desc from sys.endpoints where protocol_desc like ('%HTTP%'))
begin
insert into #httpEndpoints
select substring(name,0,25),substring(protocol_desc,0,5),substring(state_desc,0,15) from sys.endpoints where protocol_desc like ('%HTTP%')
select * from #httpEndpoints
drop table #httpEndpoints
end
Else PRINT 'Keine HTTP Endpunkte in dieser Instanz.'
PRINT ''
PRINT ''

--Ermitteln von Verbindungsservern
create table #linkedServers(Server_ID int,Name nchar(25),Produkt nchar(15),DataSource nchar(25))
PRINT 'Ermitteln von Verbindungsservern:'
PRINT''
IF  EXISTS (select server_id,name,product,data_source from sys.servers where is_linked=1)
begin
insert into #linkedServers
select server_id,substring(name,0,25),substring(product,0,15),substring(data_source,0,25) from sys.servers where is_linked=1
select * from #linkedServers
drop table #linkedServers
end
Else PRINT 'Keine Verbindungsserver in dieser Instanz.'
PRINT ''
PRINT ''

--Ermitteln des Authentifizierungsmodus
PRINT 'Ermitteln des Authentifizierungsmodus:'
PRINT ''
create table #LoginMode(Value nvarchar(9),Data int)
insert into #LoginMode execute master.dbo.xp_regread 'HKEY_LOCAL_MACHINE'
, 'SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQLServer', 'LoginMode'

SELECT  'Instanz '+substring(@@servername,0,25),LoginMode=
      CASE Data
         WHEN 1 THEN 'Windows Authentication'
         WHEN 2 THEN 'Mixed Authentication'
      END
FROM #LoginMode
drop table #LoginMode
PRINT ''
PRINT ''

--Ermitteln der Server Logins
PRINT 'Ermitteln der Server Logins:'
PRINT ''
select substring(name,0,50)
    ,'Login' =case denylogin when 1 then 'denied' else 'granted' end
    ,'DB Access' =case hasaccess when 1 then 'yes' else 'no' end
from master.sys.syslogins
PRINT ''
PRINT ''

--Ermitteln TCP/IP Port
PRINT 'Ermitteln des konfigurierten TCP/IP-Ports:'
PRINT ''
create table #tcpPort(Value nvarchar(9),Data int)
set @regkey=N'SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.'+@InstanzName+N'\MSSQLServer\SuperSocketNetLib\Tcp\IPAll'
insert into #tcpPort execute master.dbo.xp_regread 'HKEY_LOCAL_MACHINE',@regkey,'TcpPort'
SELECT  * FROM #tcpPort
drop table #tcpPort
PRINT ''
PRINT ''

----Ermitteln der Surface Area Configuration Features
PRINT 'Ermitteln der Surface Area Configuration Features'
PRINT ''

create table #sp_configure (s1 int)
insert into #sp_configure execute sp_configure 'show advanced options','1'
reconfigure
drop table #sp_configure
create table #sp_configure2 (name nvarchar(35),minimum int,maximum int,config_value int,run_value int)
insert into #sp_configure2 execute sp_configure --listet auf
select * from #sp_configure2
drop table #sp_configure2
PRINT ''
PRINT ''

Goto ScriptEnde
Abbruch:
raiserror ('Geprüfte Instanz ist kein MS SQL Server 2008, Abbruch der Abarbeitung...',17,1)
ScriptEnde:
--beendet das Script
PRINT 'Script beendet.'

Resultset zur Ausgabe in Textdatei:

Ermittelte SQL Version: Microsoft SQL Server 2008 (SP1) - 10.0.2531.0
Beginn der Evaluierung
evaluierte Instanz: SQL2008-SINGLE
Evaluierungszeitpunkt: Jun 25 2010  9:58AM                              

Ermitteln der Startart von MS SQL Diensten:

                     Startart  
-------------------- -----------
SQL Server Dienst :  Automatisch
                               
-------------------- -----------
SQL Server Agent  :  Automatisch
                               
-------------------- -----------
MSDTC             :  Automatisch
                               
-------------------- -----------
Analysis Services :  Automatisch
                               
-------------------- -----------
Reporting Services:  Automatisch
                               
-------------------- -----------
SQL Server Browser:  Automatisch


Ermitteln von HTTP Endpunkten:

Name                      Protokoll Status        
------------------------- --------- ---------------
sql_endpoint              HTTP      STARTED       


Ermitteln von Verbindungsservern:

Server_ID   Name                      Produkt         DataSource              
----------- ------------------------- --------------- -------------------------
          1 sql2005_std               SQL Server      sql2005_std             


Ermitteln des Authentifizierungsmodus:

                                  LoginMode            
--------------------------------- ----------------------
Instanz SQL2008-SINGLE            Mixed Authentication 


Ermitteln der Server Logins:

                                                   Login   DB Access
-------------------------------------------------- ------- ---------
sa_renamed                                         granted yes     
##MS_SQLResourceSigningCertificate##               granted no      
##MS_SQLReplicationSigningCertificate##            granted no      
##MS_SQLAuthenticatorCertificate##                 granted no      
##MS_PolicySigningCertificate##                    granted no      
##MS_PolicyTsqlExecutionLogin##                    granted yes     
##MS_PolicyEventProcessingLogin##                  granted yes     
##MS_AgentSigningCertificate##                     granted yes     
NT AUTHORITY\SYSTEM                                granted yes     
NT SERVICE\MSSQLSERVER                             granted yes      

NT SERVICE\SQLSERVERAGENT                          granted yes     
SQL2008-SINGLE\sqlsvc                              granted yes     
traininguser                                       denied  no      
dummyLogin                                         granted yes     
migrationUser                                      granted yes     


Ermitteln des konfigurierten TCP/IP-Ports:

Value     Data      
--------- -----------
TcpPort          1433




Ermitteln der Surface Area Configuration Features

Configuration option 'show advanced options' changed from 1 to 1. Run the RECONFIGURE statement to install.
name                                minimum     maximum     config_value run_value
----------------------------------- ----------- ----------- ------------ -----------
access check cache bucket count               0       16384            0           0
access check cache quota                      0  2147483647            0           0
Ad Hoc Distributed Queries                    0           1            1           1
affinity I/O mask                   -2147483648  2147483647            0           0
affinity mask                       -2147483648  2147483647            0           0
Agent XPs                                     0           1            1           1
allow updates                                 0           1            0           0
awe enabled                                   0           1            0           0
backup compression default                    0           1            0           0
blocked process threshold (s)                 0       86400            0           0
c2 audit mode                                 0           1            0           0
clr enabled                                   0           1            0           0
common criteria compliance enabled            0           1            0           0
cost threshold for parallelism                0       32767            5           5
cross db ownership chaining                   0           1            0           0
cursor threshold                             -1  2147483647           -1          -1
Database Mail XPs                             0           1            0           0
default full-text language                    0  2147483647         1033        1033
default language                              0        9999            0           0
default trace enabled                         0           1            1           1
disallow results from triggers                0           1            0           0
EKM provider enabled                          0           1            0           0
filestream access level                       0           2            2           2
fill factor (%)                               0         100            0           0
ft crawl bandwidth (max)                      0       32767          100         100
ft crawl bandwidth (min)                      0       32767            0           0
ft notify bandwidth (max)                     0       32767          100         100
ft notify bandwidth (min)                     0       32767            0           0
index create memory (KB)                    704  2147483647            0           0
in-doubt xact resolution                      0           2            0           0
lightweight pooling                           0           1            0           0
locks                                      5000  2147483647            0           0
max degree of parallelism                     0          64            0           0
max full-text crawl range                     0         256            4           4
max server memory (MB)                       16  2147483647   2147483647  2147483647
max text repl size (B)                       -1  2147483647        65536       65536
max worker threads                          128       32767            0           0
media retention                               0         365            0           0
min memory per query (KB)                   512  2147483647         1024        1024
min server memory (MB)                        0  2147483647            0           0
nested triggers                               0           1            1           1
network packet size (B)                     512       32767         4096        4096
Ole Automation Procedures                     0           1            0           0
open objects                                  0  2147483647            0           0
optimize for ad hoc workloads                 0           1            0           0
PH timeout (s)                                1        3600           60          60
precompute rank                               0           1            0           0
priority boost                                0           1            0           0
query governor cost limit                     0  2147483647            0           0
query wait (s)                               -1  2147483647           -1          -1
recovery interval (min)                       0       32767            0           0
remote access                                 0           1            1           1
remote admin connections                      0           1            0           0
remote login timeout (s)                      0  2147483647           20          20
remote proc trans                             0           1            0           0
remote query timeout (s)                      0  2147483647          600         600
Replication XPs                               0           1            0           0
scan for startup procs                        0           1            0           0
server trigger recursion                      0           1            1           1
set working set size                          0           1            0           0
show advanced options                         0           1            1           1
SMO and DMO XPs                               0           1            1           1
SQL Mail XPs                                  0           1            0           0
transform noise words                         0           1            0           0
two digit year cutoff                      1753        9999         2049        2049
user connections                              0       32767            0           0
user options                                  0       32767            0           0
xp_cmdshell                                   0           1            0           0