SQL Server安全配置全面检查与优化方案

alter server role [diskadmin] add member [shijw];
alter server role [processadmin] add member [shijw];
alter server role [securityadmin] add member [shijw];
alter server role [setupadmin] add member [shijw];

select name, is_policy_checked from sys.sql_logins;

select name, is_policy_checked from sys.sql_logins;

select convert(numeric(18,2), convert(numeric(18,2), c.value_in_use) / convert(numeric(18,2), maximum) * 100) as user_count
from sys.configurations c
where c.name = 'user connections';

select convert(decimal(18,0), (sum(s.current_workers_count) * 1.0 / i.max_workers_count) * 100) as cpu线程使用率
from sys.dm_os_sys_info i, sys.dm_os_schedulers s
group by i.max_workers_count;

select bs.database_name, 
       backuptype = case
           when bs.type = 'd' and bs.is_copy_only = 0 then 'full database'
           when bs.type = 'd' and bs.is_copy_only = 1 then 'full copy-only database'
           when bs.type = 'i' then 'differential database backup'
           when bs.type = 'l' then 'transaction log'
           when bs.type = 'f' then 'file or filegroup'
           when bs.type = 'g' then 'differential file'
           when bs.type = 'p' then 'partial'
           when bs.type = 'q' then 'differential partial' end + ' backup',
       case bf.device_type 
           when 2 then 'disk'
           when 5 then 'tape'
           when 7 then 'virtual device'
           when 9 then 'azure storage'
           when 105 then 'a permanent backup device'
           else 'other device' end as devicetype,
       bs.backup_start_date, 
       backupfinishdate = bs.backup_finish_date,
       [backupstatus] = case bs.backup_start_date when null then '备份失败' else '成功' end,
       latestbackuplocation = bf.physical_device_name
from msdb.dbo.backupset bs
left join msdb.dbo.backupmediafamily bf on bs.[media_set_id] = bf.[media_set_id]
where bs.backup_start_date > dateadd(month, - 2, sysdatetime())
order by bs.database_name asc, bs.backup_start_date desc;

select distinct (encrypt_option) from sys.dm_exec_connections;

select d.name as 'database name',
       case
           when e.encryption_state = 3 then 'encrypted'
           when e.encryption_state = 2 then 'in progress'
           else 'not encrypted'
       end as state
from sys.dm_database_encryption_keys e
right join sys.databases d on d.database_id = e.database_id
left join sys.certificates c on e.encryptor_thumbprint = c.thumbprint;

select name, is_encrypted from sys.databases;

select comment, value from sys.sysconfigures where comment like 'remote%';

select @@version;
select serverproperty('productversion');

select name, create_date, is_disabled from sys.sql_logins where pwdcompare('', password_hash) = 1;
select name, create_date, is_disabled from sys.sql_logins where pwdcompare(name, password_hash) = 1;

select value from sys.sysconfigures where comment = 'c2 audit mode';

select status from sys.dm_server_audit_status where status = 1;

select value from sys.sysconfigures where comment = 'enable or disable command shell';