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';