SQL SERVER SECURITY REVIEW

Report Generated on 8/28/2021 11:54 AM

SQL Server Information



ServerTypeServerType(PhysicalorVirtual)MachineNameTechnologyInstanceNameServiceAccountVersionEditionDatabasesDefault DataFile LocationDefault LogFile PathTempDB Datafile PathTempDB Logfile PathRoot PathErrorLogPath
STANDALONEPhysicalEC2AMAZ-PDG972BMSSQLEC2AMAZ-PDG972BNT Service\MSSQLSERVERMicrosoft SQL Server 2019 (RTM-CU10) (KB5001090)Standard Edition (64-bit) 1.MASTER, 2.TEMPDB, 3.MODEL, 4.MSDB, 5.SQLFLEETREPOC:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\tempdb.mdfC:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\templog.ldfC:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQLC:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Log

Database Owners



DatabaseOwnerOwner Type
SQLFleetRepo

Windows Authentication Logins



LoginTypeDisabled?Create DateDefault Database
BUILTIN\AdministratorsWINDOWS_GROUPN11/4/2019 4:53:51 AMmaster
NT SERVICE\SQLWriterWINDOWS_LOGINN11/4/2019 4:53:51 AMmaster
NT SERVICE\WinmgmtWINDOWS_LOGINN11/4/2019 4:53:51 AMmaster
NT Service\MSSQLSERVERWINDOWS_LOGINN11/4/2019 4:53:51 AMmaster
NT AUTHORITY\SYSTEMWINDOWS_LOGINN11/4/2019 4:53:51 AMmaster
NT SERVICE\SQLSERVERAGENTWINDOWS_LOGINN11/4/2019 4:53:52 AMmaster
NT SERVICE\SQLTELEMETRYWINDOWS_LOGINN11/4/2019 4:53:53 AMmaster

SQL Server Authentication Logins



LoginTypeDisabled?Create DateDefault DatabaseEnforce Password PolicyEnforce Password Expiration
##MS_PolicyEventProcessingLogin##SQL_LOGINY5/12/2021 4:51:06 AMmasterYN
##MS_PolicyTsqlExecutionLogin##SQL_LOGINY9/24/2019 2:21:53 PMmasterYN
pythianSQL_LOGINN7/8/2021 5:03:45 PMmasterNN
saSQL_LOGINY4/8/2003 9:10:35 AMmasterYN
testSQL_LOGINN7/6/2021 5:46:32 PMmasterNN

Server Level Permissions



Grantee LoginTypeGrantor LoginPermissionState
##MS_PolicyEventProcessingLogin##SQL_LOGINsaCONNECT SQLGRANT
##MS_PolicyTsqlExecutionLogin##SQL_LOGINsaCONNECT SQLGRANT
##MS_PolicyTsqlExecutionLogin##SQL_LOGINsaVIEW ANY DEFINITIONGRANT
##MS_PolicyTsqlExecutionLogin##SQL_LOGINsaVIEW SERVER STATEGRANT
BUILTIN\AdministratorsWINDOWS_GROUPsaCONNECT SQLGRANT
NT AUTHORITY\SYSTEMWINDOWS_LOGINsaALTER ANY AVAILABILITY GROUPGRANT
NT AUTHORITY\SYSTEMWINDOWS_LOGINsaCONNECT SQLGRANT
NT AUTHORITY\SYSTEMWINDOWS_LOGINsaVIEW SERVER STATEGRANT
NT Service\MSSQLSERVERWINDOWS_LOGINsaCONNECT SQLGRANT
NT SERVICE\SQLSERVERAGENTWINDOWS_LOGINsaCONNECT SQLGRANT
NT SERVICE\SQLTELEMETRYWINDOWS_LOGINsaALTER ANY EVENT SESSIONGRANT
NT SERVICE\SQLTELEMETRYWINDOWS_LOGINsaCONNECT ANY DATABASEGRANT
NT SERVICE\SQLTELEMETRYWINDOWS_LOGINsaCONNECT SQLGRANT
NT SERVICE\SQLTELEMETRYWINDOWS_LOGINsaVIEW ANY DEFINITIONGRANT
NT SERVICE\SQLTELEMETRYWINDOWS_LOGINsaVIEW SERVER STATEGRANT
NT SERVICE\SQLWriterWINDOWS_LOGINsaCONNECT SQLGRANT
NT SERVICE\WinmgmtWINDOWS_LOGINsaCONNECT SQLGRANT
publicSERVER_ROLEsaCONNECTGRANT
publicSERVER_ROLEsaCONNECTGRANT
publicSERVER_ROLEsaCONNECTGRANT
publicSERVER_ROLEsaCONNECTGRANT
publicSERVER_ROLEsaVIEW ANY DATABASEGRANT
pythianSQL_LOGINsaCONNECT SQLGRANT
saSQL_LOGINsaCONNECT SQLGRANT
testSQL_LOGINsaCONNECT SQLGRANT

Server Role Members



LoginRole
BUILTIN\Administratorssysadmin
NT Service\MSSQLSERVERsysadmin
NT SERVICE\SQLSERVERAGENTsysadmin
NT SERVICE\SQLWritersysadmin
NT SERVICE\Winmgmtsysadmin
pythiansysadmin
sasysadmin
testsysadmin

Database Level Permissions



DatabaseGrantee LoginTypeGrantor LoginPermissionApplies ToState
master##MS_PolicyEventProcessingLogin##SQL_USERdboCONNECTDATABASEGRANT
master##MS_PolicyEventProcessingLogin##SQL_USERdboEXECUTEOBJECT_OR_COLUMNGRANT
masterdboSQL_USERdboCONNECTDATABASEGRANT
masterguestSQL_USERdboCONNECTDATABASEGRANT
masterpublicDATABASE_ROLEdboEXECUTEOBJECT_OR_COLUMNGRANT
masterpublicDATABASE_ROLEdboSELECTOBJECT_OR_COLUMNGRANT
masterpublicDATABASE_ROLEdboVIEW ANY COLUMN ENCRYPTION KEY DEFINITIONDATABASEGRANT
masterpublicDATABASE_ROLEdboVIEW ANY COLUMN MASTER KEY DEFINITIONDATABASEGRANT
modeldboSQL_USERdboCONNECTDATABASEGRANT
modelpublicDATABASE_ROLEdboSELECTOBJECT_OR_COLUMNGRANT
modelpublicDATABASE_ROLEdboVIEW ANY COLUMN ENCRYPTION KEY DEFINITIONDATABASEGRANT
modelpublicDATABASE_ROLEdboVIEW ANY COLUMN MASTER KEY DEFINITIONDATABASEGRANT
msdb##MS_PolicyEventProcessingLogin##SQL_USERdboCONNECTDATABASEGRANT
msdb##MS_PolicyEventProcessingLogin##SQL_USERdboEXECUTEOBJECT_OR_COLUMNGRANT
msdb##MS_PolicyTsqlExecutionLogin##SQL_USERdboCONNECTDATABASEGRANT
msdbDatabaseMailUserRoleDATABASE_ROLEdboEXECUTEOBJECT_OR_COLUMNGRANT
msdbDatabaseMailUserRoleDATABASE_ROLEdboSELECTOBJECT_OR_COLUMNGRANT
msdbdb_ssisadminDATABASE_ROLEdboDELETEOBJECT_OR_COLUMNGRANT
msdbdb_ssisadminDATABASE_ROLEdboEXECUTEOBJECT_OR_COLUMNGRANT
msdbdb_ssisadminDATABASE_ROLEdboINSERTOBJECT_OR_COLUMNGRANT
msdbdb_ssisadminDATABASE_ROLEdboREFERENCESOBJECT_OR_COLUMNGRANT
msdbdb_ssisadminDATABASE_ROLEdboSELECTOBJECT_OR_COLUMNGRANT
msdbdb_ssisadminDATABASE_ROLEdboUPDATEOBJECT_OR_COLUMNGRANT
msdbdb_ssisltduserDATABASE_ROLEdboEXECUTEOBJECT_OR_COLUMNGRANT
msdbdb_ssisltduserDATABASE_ROLEdboINSERTOBJECT_OR_COLUMNGRANT
msdbdb_ssisltduserDATABASE_ROLEdboSELECTOBJECT_OR_COLUMNGRANT
msdbdb_ssisoperatorDATABASE_ROLEdboEXECUTEOBJECT_OR_COLUMNGRANT
msdbdb_ssisoperatorDATABASE_ROLEdboINSERTOBJECT_OR_COLUMNGRANT
msdbdb_ssisoperatorDATABASE_ROLEdboSELECTOBJECT_OR_COLUMNGRANT
msdbdboSQL_USERdboCONNECTDATABASEGRANT
msdbdc_adminDATABASE_ROLEdboEXECUTEOBJECT_OR_COLUMNGRANT
msdbdc_adminDATABASE_ROLEdboEXECUTEXML_SCHEMA_COLLECTIONGRANT
msdbdc_adminDATABASE_ROLEMS_DataCollectorInternalUserIMPERSONATEDATABASE_PRINCIPALGRANT
msdbdc_adminDATABASE_ROLEdboVIEW DEFINITIONXML_SCHEMA_COLLECTIONGRANT
msdbdc_operatorDATABASE_ROLEdboEXECUTEOBJECT_OR_COLUMNGRANT
msdbdc_operatorDATABASE_ROLEdboSELECTOBJECT_OR_COLUMNGRANT
msdbdc_proxyDATABASE_ROLEdboEXECUTEOBJECT_OR_COLUMNGRANT
msdbdc_proxyDATABASE_ROLEdboSELECTOBJECT_OR_COLUMNGRANT
msdbguestSQL_USERdboCONNECTDATABASEGRANT
msdbMS_DataCollectorInternalUserSQL_USERdboCONNECTDATABASEGRANT
msdbPolicyAdministratorRoleDATABASE_ROLEdboEXECUTEOBJECT_OR_COLUMNGRANT
msdbPolicyAdministratorRoleDATABASE_ROLEdboSELECTOBJECT_OR_COLUMNGRANT
msdbpublicDATABASE_ROLEdboEXECUTEOBJECT_OR_COLUMNGRANT
msdbpublicDATABASE_ROLEdboSELECTOBJECT_OR_COLUMNGRANT
msdbpublicDATABASE_ROLEdboVIEW ANY COLUMN ENCRYPTION KEY DEFINITIONDATABASEGRANT
msdbpublicDATABASE_ROLEdboVIEW ANY COLUMN MASTER KEY DEFINITIONDATABASEGRANT
msdbServerGroupAdministratorRoleDATABASE_ROLEdboEXECUTEOBJECT_OR_COLUMNGRANT
msdbServerGroupReaderRoleDATABASE_ROLEdboSELECTOBJECT_OR_COLUMNGRANT
msdbSQLAgentOperatorRoleDATABASE_ROLEdboEXECUTEOBJECT_OR_COLUMNGRANT
msdbSQLAgentOperatorRoleDATABASE_ROLEdboSELECTOBJECT_OR_COLUMNGRANT
msdbSQLAgentUserRoleDATABASE_ROLEdboEXECUTEOBJECT_OR_COLUMNGRANT
msdbSQLAgentUserRoleDATABASE_ROLEdboSELECTOBJECT_OR_COLUMNGRANT
msdbTargetServersRoleDATABASE_ROLEdboDELETEOBJECT_OR_COLUMNGRANT
msdbTargetServersRoleDATABASE_ROLEdboEXECUTEOBJECT_OR_COLUMNDENY
msdbTargetServersRoleDATABASE_ROLEdboEXECUTEOBJECT_OR_COLUMNGRANT
msdbTargetServersRoleDATABASE_ROLEdboSELECTOBJECT_OR_COLUMNGRANT
msdbTargetServersRoleDATABASE_ROLEdboUPDATEOBJECT_OR_COLUMNGRANT
msdbUtilityCMRReaderDATABASE_ROLEdboEXECUTEOBJECT_OR_COLUMNGRANT
msdbUtilityCMRReaderDATABASE_ROLEdboSELECTOBJECT_OR_COLUMNGRANT
msdbUtilityIMRReaderDATABASE_ROLEdboEXECUTEOBJECT_OR_COLUMNGRANT
msdbUtilityIMRReaderDATABASE_ROLEdboSELECTOBJECT_OR_COLUMNGRANT
msdbUtilityIMRWriterDATABASE_ROLEdboDELETEOBJECT_OR_COLUMNGRANT
msdbUtilityIMRWriterDATABASE_ROLEdboEXECUTEOBJECT_OR_COLUMNGRANT
msdbUtilityIMRWriterDATABASE_ROLEdboINSERTOBJECT_OR_COLUMNGRANT
msdbUtilityIMRWriterDATABASE_ROLEdboSELECTOBJECT_OR_COLUMNGRANT
SQLFleetRepodboWINDOWS_USERdboCONNECTDATABASEGRANT
SQLFleetRepopublicDATABASE_ROLEdboSELECTOBJECT_OR_COLUMNGRANT
SQLFleetRepopublicDATABASE_ROLEdboVIEW ANY COLUMN ENCRYPTION KEY DEFINITIONDATABASEGRANT
SQLFleetRepopublicDATABASE_ROLEdboVIEW ANY COLUMN MASTER KEY DEFINITIONDATABASEGRANT
tempdbdboSQL_USERdboCONNECTDATABASEGRANT
tempdbguestSQL_USERdboCONNECTDATABASEGRANT
tempdbpublicDATABASE_ROLEdboSELECTOBJECT_OR_COLUMNGRANT
tempdbpublicDATABASE_ROLEdboVIEW ANY COLUMN ENCRYPTION KEY DEFINITIONDATABASEGRANT
tempdbpublicDATABASE_ROLEdboVIEW ANY COLUMN MASTER KEY DEFINITIONDATABASEGRANT

Database Role Members



DatabaseLoginRole
masterdbodb_owner
modeldbodb_owner
msdbdbodb_owner
msdbdc_operatordb_ssisltduser
msdbdc_proxydb_ssisltduser
msdbdc_operatordb_ssisoperator
msdbdc_proxydb_ssisoperator
msdbMS_DataCollectorInternalUserdb_ssisoperator
msdbMS_DataCollectorInternalUserdc_admin
msdbdc_admindc_operator
msdb##MS_PolicyEventProcessingLogin##PolicyAdministratorRole
msdb##MS_PolicyTsqlExecutionLogin##PolicyAdministratorRole
msdbServerGroupAdministratorRoleServerGroupReaderRole
msdbPolicyAdministratorRoleSQLAgentOperatorRole
msdbSQLAgentOperatorRoleSQLAgentReaderRole
msdbdc_operatorSQLAgentUserRole
msdbMS_DataCollectorInternalUserSQLAgentUserRole
msdbSQLAgentReaderRoleSQLAgentUserRole
msdbUtilityIMRWriterUtilityIMRReader
SQLFleetRepodbodb_owner
tempdbdbodb_owner

SQL Server Job Owners



JobOwnerLogin Type
syspolicy_purge_historysaSQL Login

Login Account for SQL Services



Service NameService AccountStartMode
SQL Server Integration Services 15.0NT Service\MsDtsServer150Auto
SQL Server (SAMPLE1)NT Service\MSSQL$SAMPLE1Auto
SQL Full-text Filter Daemon Launcher (MSSQLSERVER)NT Service\MSSQLFDLauncherManual
SQL Server Launchpad (MSSQLSERVER)NT Service\MSSQLLaunchpadAuto
SQL Server (MSSQLSERVER)NT Service\MSSQLSERVERAuto
SQL Server Analysis Services (MSSQLSERVER)NT Service\MSSQLServerOLAPServiceAuto
SQL Server Distributed Replay ClientNT Service\SQL Server Distributed Replay ClientManual
SQL Server Distributed Replay ControllerNT Service\SQL Server Distributed Replay ControllerManual
SQL Server Agent (SAMPLE1)NT Service\SQLAgent$SAMPLE1Auto
SQL Server BrowserNT AUTHORITY\LOCALSERVICEDisabled
SQL Server Agent (MSSQLSERVER)NT Service\SQLSERVERAGENTAuto
SQL Server Reporting ServicesNT SERVICE\SQLServerReportingServicesAuto
SQL Server CEIP service (MSSQLSERVER)NT Service\SQLTELEMETRYAuto
SQL Server CEIP service (SAMPLE1)NT Service\SQLTELEMETRY$SAMPLE1Auto
SQL Server VSS WriterLocalSystemAuto
SQL Server Analysis Services CEIP (MSSQLSERVER)NT Service\SSASTELEMETRYAuto
SQL Server Integration Services Scale Out Worker 15.0NT Service\SSISScaleOutWorker150Auto
SQL Server Integration Services CEIP service 15.0NT Service\SSISTELEMETRY150Auto

SQL Server Network Protocols



Protocol NameIsEnabled?
Shared MemoryTrue
Named PipesFalse
TCP\IPTrue

SQL TCP Port Settings



SQL Server Instance [EC2AMAZ-PDG972B] is set to use Static Port # : 1433.


SQL Server Login Auditing



Login AuditingRecommended Setting
Failed Logins OnlyY

SQL Sever Global Configuration Parameters



Configuration ParameterIsEnabled?
default trace enabledEnabled
remote accessEnabled
SMO and DMO XPsEnabled
xp_cmdshellDisbaled


END OF REPORT