SQL Serverhardening guide

SELECT
    servicename,
    startup_type_desc,
    status_desc,
    service_account,
    instant_file_initialization_enabled,
    last_startup_time
FROM sys.dm_server_services
ORDER BY servicename;

SELECT
    sp.name AS login_name,
    sp.type_desc,
    sp.is_disabled,
    sp.create_date,
    sp.modify_date,
    sp.default_database_name,
    sl.is_policy_checked,
    sl.is_expiration_checked,
    LOGINPROPERTY(sp.name, 'PasswordLastSetTime') AS password_last_set_time,
    sp.credential_id
FROM sys.server_principals AS sp
LEFT JOIN sys.sql_logins AS sl
    ON sl.principal_id = sp.principal_id
WHERE sp.type IN ('S', 'U', 'G', 'E', 'X')
  AND sp.name NOT LIKE N'##MS_%##'
ORDER BY sp.is_disabled, sp.type_desc, sp.name;

SELECT
    role_principal.name AS server_role,
    member_principal.name AS member_name,
    member_principal.type_desc AS member_type,
    member_principal.is_disabled,
    member_principal.default_database_name
FROM sys.server_role_members AS srm
JOIN sys.server_principals AS role_principal
    ON role_principal.principal_id = srm.role_principal_id
JOIN sys.server_principals AS member_principal
    ON member_principal.principal_id = srm.member_principal_id
ORDER BY role_principal.name, member_principal.name;

SELECT
    grantee.name AS grantee_name,
    grantee.type_desc AS grantee_type,
    permission.state_desc,
    permission.permission_name,
    permission.class_desc,
    grantor.name AS grantor_name
FROM sys.server_permissions AS permission
JOIN sys.server_principals AS grantee
    ON grantee.principal_id = permission.grantee_principal_id
LEFT JOIN sys.server_principals AS grantor
    ON grantor.principal_id = permission.grantor_principal_id
ORDER BY grantee.name, permission.class_desc, permission.permission_name;

USE [YourDatabase];

SELECT
    DB_NAME() AS database_name,
    role_principal.name AS database_role,
    member_principal.name AS member_name,
    member_principal.type_desc AS member_type,
    member_principal.authentication_type_desc,
    member_principal.create_date,
    member_principal.modify_date
FROM sys.database_role_members AS drm
JOIN sys.database_principals AS role_principal
    ON role_principal.principal_id = drm.role_principal_id
JOIN sys.database_principals AS member_principal
    ON member_principal.principal_id = drm.member_principal_id
ORDER BY role_principal.name, member_principal.name;

USE [YourDatabase];

SELECT
    DB_NAME() AS database_name,
    grantee.name AS grantee_name,
    grantee.type_desc AS grantee_type,
    permission.state_desc,
    permission.permission_name,
    permission.class_desc,
    OBJECT_SCHEMA_NAME(permission.major_id) AS object_schema,
    OBJECT_NAME(permission.major_id) AS object_name,
    grantor.name AS grantor_name
FROM sys.database_permissions AS permission
JOIN sys.database_principals AS grantee
    ON grantee.principal_id = permission.grantee_principal_id
LEFT JOIN sys.database_principals AS grantor
    ON grantor.principal_id = permission.grantor_principal_id
WHERE grantee.name NOT IN (N'public', N'guest', N'INFORMATION_SCHEMA', N'sys')
ORDER BY grantee.name, permission.class_desc, permission.permission_name;

SELECT
    d.name AS database_name,
    SUSER_SNAME(d.owner_sid) AS database_owner,
    d.containment_desc,
    d.is_trustworthy_on,
    d.is_db_chaining_on,
    d.is_broker_enabled,
    d.is_encrypted,
    d.state_desc,
    d.user_access_desc
FROM sys.databases AS d
WHERE d.database_id > 4
ORDER BY d.name;

SELECT
    name,
    value,
    value_in_use,
    is_dynamic,
    is_advanced,
    description
FROM sys.configurations
WHERE name IN (
    N'Ad Hoc Distributed Queries',
    N'clr enabled',
    N'clr strict security',
    N'cross db ownership chaining',
    N'Database Mail XPs',
    N'external scripts enabled',
    N'Ole Automation Procedures',
    N'remote access',
    N'remote admin connections',
    N'scan for startup procs',
    N'xp_cmdshell'
)
ORDER BY name;

SELECT
    e.name AS endpoint_name,
    e.type_desc,
    e.protocol_desc,
    e.state_desc,
    e.is_admin_endpoint,
    te.port,
    te.ip_address
FROM sys.endpoints AS e
LEFT JOIN sys.tcp_endpoints AS te
    ON te.endpoint_id = e.endpoint_id
ORDER BY e.type_desc, e.name;

SELECT
    s.name AS linked_server_name,
    s.product,
    s.provider,
    s.data_source,
    s.is_linked,
    s.is_remote_login_enabled,
    s.is_rpc_out_enabled,
    s.is_data_access_enabled,
    local_principal.name AS local_principal_name,
    ll.uses_self_credential,
    ll.remote_name
FROM sys.servers AS s
LEFT JOIN sys.linked_logins AS ll
    ON ll.server_id = s.server_id
LEFT JOIN sys.server_principals AS local_principal
    ON local_principal.principal_id = ll.local_principal_id
WHERE s.is_linked = 1
ORDER BY s.name, local_principal.name;

SELECT
    name AS credential_name,
    credential_identity,
    create_date,
    modify_date,
    target_type,
    target_id
FROM sys.credentials
ORDER BY name;

USE [YourDatabase];

SELECT
    name AS database_scoped_credential_name,
    credential_identity,
    create_date,
    modify_date,
    target_type,
    target_id
FROM sys.database_scoped_credentials
ORDER BY name;

USE msdb;

SELECT
    p.name AS proxy_name,
    p.enabled,
    c.name AS credential_name,
    SUSER_SNAME(pl.sid) AS proxy_principal_name,
    ss.subsystem,
    p.description
FROM dbo.sysproxies AS p
LEFT JOIN sys.credentials AS c
    ON c.credential_id = p.credential_id
LEFT JOIN dbo.sysproxylogin AS pl
    ON pl.proxy_id = p.proxy_id
LEFT JOIN dbo.sysproxysubsystem AS ps
    ON ps.proxy_id = p.proxy_id
LEFT JOIN dbo.syssubsystems AS ss
    ON ss.subsystem_id = ps.subsystem_id
ORDER BY p.name, proxy_principal_name, ss.subsystem;

USE msdb;

SELECT
    j.name AS job_name,
    SUSER_SNAME(j.owner_sid) AS job_owner,
    j.enabled AS job_enabled,
    s.step_id,
    s.step_name,
    s.subsystem,
    p.name AS proxy_name,
    c.name AS credential_name
FROM dbo.sysjobs AS j
LEFT JOIN dbo.sysjobsteps AS s
    ON s.job_id = j.job_id
LEFT JOIN dbo.sysproxies AS p
    ON p.proxy_id = s.proxy_id
LEFT JOIN sys.credentials AS c
    ON c.credential_id = p.credential_id
ORDER BY j.name, s.step_id;

USE master;

SELECT
    name AS certificate_name,
    subject,
    issuer_name,
    start_date,
    expiry_date,
    pvt_key_encryption_type_desc,
    pvt_key_last_backup_date,
    key_length
FROM sys.certificates
WHERE name NOT LIKE N'##MS_%##'
ORDER BY expiry_date, name;

SELECT
    DB_NAME(dek.database_id) AS database_name,
    dek.encryption_state,
    CASE dek.encryption_state
        WHEN 0 THEN 'No database encryption key'
        WHEN 1 THEN 'Unencrypted'
        WHEN 2 THEN 'Encryption in progress'
        WHEN 3 THEN 'Encrypted'
        WHEN 4 THEN 'Key change in progress'
        WHEN 5 THEN 'Decryption in progress'
        WHEN 6 THEN 'Protection change in progress'
    END AS encryption_state_desc,
    dek.percent_complete,
    dek.key_algorithm,
    dek.key_length,
    dek.encryptor_type
FROM sys.dm_database_encryption_keys AS dek
ORDER BY database_name;

SELECT
    a.name AS audit_name,
    a.is_state_enabled AS audit_enabled,
    a.type_desc AS audit_target_type,
    a.queue_delay,
    a.on_failure_desc,
    sas.name AS server_audit_specification,
    sas.is_state_enabled AS server_spec_enabled,
    sad.audit_action_name,
    sad.audited_result,
    sad.class_desc,
    sad.major_id
FROM sys.server_audits AS a
LEFT JOIN sys.server_audit_specifications AS sas
    ON sas.audit_guid = a.audit_guid
LEFT JOIN sys.server_audit_specification_details AS sad
    ON sad.server_specification_id = sas.server_specification_id
ORDER BY a.name, sas.name, sad.audit_action_name;

USE [YourDatabase];

SELECT
    das.name AS database_audit_specification,
    das.is_state_enabled AS database_spec_enabled,
    dad.audit_action_name,
    dad.audited_result,
    dad.class_desc,
    dad.major_id
FROM sys.database_audit_specifications AS das
LEFT JOIN sys.database_audit_specification_details AS dad
    ON dad.database_specification_id = das.database_specification_id
ORDER BY das.name, dad.audit_action_name;