SQL Server审计功能使用示例

目的：监控普通用户对数据库的非SELECT操作

操作步骤：

--1. 创建审计，来监控对数据库的所有操作

--GUI操作方法：在数据库实例下--安全性--审计--新建审核

USE [master]

GO

CREATE SERVER AUDIT [AuditLog]

TO FILE 

( FILEPATH = N'E:\SQLAuditLog'

,MAXSIZE = 1 GB

,MAX_ROLLOVER_FILES = 2147483647

,RESERVE_DISK_SPACE = OFF

) WITH (QUEUE_DELAY = 1000, ON_FAILURE = CONTINUE)

GO

--完成后在GUI界面右键，启用审核。

--2. 对应数据库下创建审核规范

--GUI操作方法：对应数据库-安全性-数据库审核规范--右键新建数据库审核规范

USE [TESTDB]

GO

CREATE DATABASE AUDIT SPECIFICATION [DatabaseAuditSpecification-20240124-143021]

FOR SERVER AUDIT [AuditLog]

ADD (INSERT, UPDATE, DELETE ON DATABASE::[TESTDB] BY [it]),

ADD (INSERT, UPDATE, DELETE ON DATABASE::[TESTDB] BY [max])

GO

--3. 查询审核日志

--GUI方法：数据库实例-安全性-审核-右键此前创建的审核-查看审核日志

SELECT event_time,action_id,succeeded,session_id,session_server_principal_name,object_name,statement,file_name,audit_file_offset

FROM sys.fn_get_audit_file('E:\AduitLog\Audit-20240119-091132_3B8B4864-F6A0-48A9-9964-58FA24A6E096_0_133501004286830000.sqlaudit',DEFAULT,DEFAULT) 

ORDER BY 1 DESC; 

代码说明：斜体字可根据实际情况调整

未解决问题：日志中存在sa的相关操作记录，导致日志文件很大。

Holy crap!是什么意思？

 "holy crap"是一种略显粗俗的感叹词，表示惊讶。

从语言学角度看，这里有几件事。"crap"是 "shit"的一个不太粗俗的替代词。也就是字面上的粪便或排便行为。

至于"holy"的部分，那是早期宗教时代遗留下来的。几个世纪前，基督教在英语社会的日常生活中更为突出。亵渎神明或"亵渎主的圣名"可能会引起冒犯--这也是发誓的意义之一。因此，不礼貌的感叹词会使用宗教术语:"Holy Christ!" "Holy Mother of God"。今天的社会更加世俗化，宗教色彩越来越淡。所以，说 "Damn"是一种温和的蔑称，不会引起人们的注意。今天令人反感的不是滥用宗教术语，而是粗俗。因此，在 "holy shit"这样的结构中，英语保留了形式（以 "holy"开头），但用粗俗的词语取代了另一个宗教词语。因此，"holy crap"将被视为"minced oath"。也就是说，它用一个温和的形容词代替了强烈的形容词，就像用 "gosh darn"代替了"God damn"。

用批处理文件监控程序运行状况，不运行时自动发邮件通知。

我有个长驻内存的程序，有时候会自动停止运行，本来想用批处理监控它并自动启动的，但一直失败，只能手动双击exe文件启动，所以迫不得已只能监测到停止时自动发邮件了。

好了，宝子们，上代码！😄

检测程序运行状况的批处理文件xxx.bat的内容：

tasklist | find /i "autotask.exe" && echo "It is running"  || "D:\sendmail.bat"

发送邮件的批处理文件sendmail.bat的内容：

echo set sh=WScript.CreateObject("WScript.Shell") >tmp.vbs
rem 停顿0.3s
echo WScript.Sleep 300 >>tmp.vbs
rem 下面的服务器地址替换成自己的，端口我只测了25
echo sh.SendKeys "open mail.domain.com 25{ENTER}" >>tmp.vbs
echo WScript.Sleep 1000 >>tmp.vbs
echo sh.SendKeys "helo abc{ENTER}" >>tmp.vbs
echo WScript.Sleep 300 >>tmp.vbs
rem 应该还要认证的，但我的服务器做了免认证，所以略过了。请自行Google吧。
rem 发件人
echo sh.SendKeys "mail from:<sender@domain.com>{ENTER}" >>tmp.vbs
echo WScript.Sleep 300 >>tmp.vbs
rem 收件人
echo sh.SendKeys "rcpt to:<yourname@domain.com>{ENTER}" >>tmp.vbs
echo WScript.Sleep 300 >>tmp.vbs
echo sh.SendKeys "data{ENTER}" >>tmp.vbs
rem 邮件头中的发件人和收件人
echo sh.SendKeys "from:sender@domain.com{ENTER}" >>tmp.vbs
echo sh.SendKeys "to:yourname@domain.com{ENTER}" >>tmp.vbs
cho WScript.Sleep 300 >>tmp.vbs
rem 邮件主题
echo sh.SendKeys "subject:AutoTask is not running{ENTER}{ENTER}" >>tmp.vbs
echo WScript.Sleep 300 >>tmp.vbs
rem 邮件内容，可多行。
echo sh.SendKeys "Here is the first line of your message body.{ENTER}" >>tmp.vbs
echo sh.SendKeys "Here is the second line of your message body.{ENTER}" >>tmp.vbs
rem 用一个点（.）结束邮件
echo sh.SendKeys ".{ENTER}" >>tmp.vbs
start telnet
rem 执行vbs文件，发送邮件。
cscript //nologo tmp.vbs
rem 清空vbs文件
echo sh.SendKeys "{ENTER}" >tmp.vbs

将.exe文件注册为windows服务时的"坑"

为了让.exe命令能像服务一样在未登录Windows时自动启动，我们可以将符合服务规范的.exe文件注册为windows服务，使用的命令是sc，具体使用方法在网上有很多，就不赘述了。但sc命令中的坑需要记录一下：=（等号）前面不能有空格，而=（等号）后面必须有一个空格，切记！

另外，要以管理员的身份打开命令行。

SQL Server从BAK备份文件还原到新数据库

 

USE [master]
restore database [Db_TEMP]  --新的数据库名称
from disk = 'D:\BACKUP\Db_backup.bak'  --备份文件所在位置
with nounload, replace, stats = 10,
MOVE N'Db' to N'D:\temp\Db_TEMP.mdf',  --MOVE后的Db是逻辑名称，To后面的是文件存放的物理位置。
MOVE N'Db_log' to N'D:\temp\Db_TEMP.ldf'  --同上，逻辑名称可以通过RESTORE FILELISTONLY from disk = 'D:\BACKUP\Db_backup.bak'查到，在结果的第1列。
GO

 上面的逻辑名称很重要，否则会提示“逻辑文件 'xxx' 不是数据库 'xxx' 的一部分” 这样的错误。

SQL Server 查询表占用空间大小的语句

 

--表占用空间情况
SELECT db_name() as DbName,
    t.NAME AS TableName,
    s.Name AS SchemaName,
    p.rows AS RowCounts,
    SUM(a.total_pages) * 8 AS TotalSpaceKB,
    CAST(ROUND(((SUM(a.total_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS 总共占用空间MB,
    SUM(a.used_pages) * 8 AS UsedSpaceKB,
    CAST(ROUND(((SUM(a.used_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS UsedSpaceMB,
    (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB,
    CAST(ROUND(((SUM(a.total_pages) - SUM(a.used_pages)) * 8) / 1024.00, 2) AS NUMERIC(36, 2)) AS UnusedSpaceMB
FROM
    sys.tables t
INNER JOIN      
    sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN
    sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN
    sys.allocation_units a ON p.partition_id = a.container_id
LEFT OUTER JOIN
    sys.schemas s ON t.schema_id = s.schema_id
WHERE
    t.NAME NOT LIKE 'dt%'
    AND t.is_ms_shipped = 0
    AND i.OBJECT_ID > 0
GROUP BY
    t.Name, s.Name, p.Rows
ORDER BY
    总共占用空间MB desc