- html - 出于某种原因,IE8 对我的 Sass 文件中继承的 html5 CSS 不友好?
- JMeter 在响应断言中使用 span 标签的问题
- html - 在 :hover and :active? 上具有不同效果的 CSS 动画
- html - 相对于居中的 html 内容固定的 CSS 重复背景?
我正在尝试为在 Web 应用程序、SQL Server 代理作业和手动运行数据库查询中执行的操作创建审计跟踪。我正在尝试使用触发器来捕获某些表上的更新、插入和删除。
总的来说,这个过程是有效的。例如,用户在 Web 应用程序中执行更新,触发器将更新的数据写入我定义的审计跟踪表,包括执行该操作的人的用户名。从 Web 应用程序或手动查询的角度来看,这工作得很好,但我们也有几十个 SQL Server 代理作业,我想捕获其中一个运行特定查询。每个代理作业都使用相同的用户名运行。这也能正常工作,并将用户名正确输入到表中,但我找不到调用此查询的作业。
我当前的“解决方案”是查找触发时当前正在运行的作业,因为其中之一必须是正确的。使用:
CREATE TABLE #xp_results
(
job_id UNIQUEIDENTIFIER NOT NULL,
last_run_date INT NOT NULL,
last_run_time INT NOT NULL,
next_run_date INT NOT NULL,
next_run_time INT NOT NULL,
next_run_schedule_id INT NOT NULL,
requested_to_run INT NOT NULL, -- BOOL
request_source INT NOT NULL,
request_source_id sysname COLLATE database_default NULL,
running INT NOT NULL, -- BOOL
current_step INT NOT NULL,
current_retry_attempt INT NOT NULL,
job_state INT NOT NULL
)
INSERT INTO #xp_results
EXECUTE master.dbo.xp_sqlagent_enum_jobs 1, 'sa'
SELECT @runningJobs = STUFF((SELECT ',' + j.name
FROM #xp_results r
INNER JOIN msdb..sysjobs j ON r.job_id = j.job_id
WHERE running = 1
FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '')
DROP TABLE #xp_results
我运行了一个特定的作业来测试它似乎工作,因为任何正在运行的其他作业都将列在 @runningJobs
中,但它不会记录运行它的作业。我假设触发器运行时作业已经完成。
有什么方法可以找出哪个作业调用了启动触发器的查询?
编辑:我尝试更改上面的 SELECT
查询以获取过去 2 分钟内运行或当前正在运行的任何作业。 SQL 查询现在是:
SELECT @runningJobs = STUFF((SELECT ',' + j.name
FROM #xp_results r
INNER JOIN msdb..sysjobs j ON r.job_id = j.job_id
WHERE (last_run_date = CAST(REPLACE(LEFT(CONVERT(VARCHAR, getdate(), 120), 10), '-', '') AS INT)
AND last_run_time > CAST(REPLACE(LEFT(CONVERT(VARCHAR,getdate(),108), 8), ':', '') AS INT) - 200)
OR running = 1
FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '')
当我运行一个作业,然后在作业运行时运行上面的查询,返回正确的作业。但是当 SSIS 包运行时,无论是通过 SQL Server 代理作业还是在 SSIS 中手动运行,@runningJobs
都不会被填充,只会返回 NULL
。
所以我现在认为这是 SSIS 和 master.dbo.xp_sqlagent_enum_jobs
权限的问题。还有其他想法吗?
编辑 #2:实际上不认为这是权限错误。此代码下方有一个 INSERT
语句,如果它是权限错误,则 INSERT
语句不会运行,因此审计行不会添加到数据库中。因此,由于在数据库中添加了一行,只是没有填充 runningJobs
字段。奇怪的时代。
编辑#3:我只是想澄清一下,我正在寻找一种解决方案,不需要我参与每项工作并做出任何改变。有太多的工作使这成为一个可行的解决方案。
最佳答案
工作代码在第一次编辑中 - (anothershrubery)
使用 app_name()
函数 http://msdn.microsoft.com/en-us/library/ms189770.aspx在您的审核触发器中获取运行查询的应用程序的名称。
对于 SQL 代理作业,app_name 在应用程序名称中包含作业步骤 ID(如果是 T-SQL 步骤)。我们在我们的审计触发器中这样做并且效果很好。从审核触发器中运行时的 app_name()
结果示例:
SQLAgent - TSQL JobStep (Job 0x96EB56A24786964889AB504D9A920D30 : Step 1)
可以通过 msdb.dbo.sysjobs_view
中的 job_id
列查找此作业。
由于 SSIS 包在 SQL 代理作业引擎之外启动 SQL 连接,这些连接将有自己的应用程序名称,您需要在 SSIS 包的连接字符串中设置应用程序名称。在 SSIS 包、Web 应用程序、WinForms 或连接到 SQL Server 的任何客户端中,您可以通过在连接字符串中使用以下内容来设置 app_name 函数返回的值:
"Application Name=MyAppNameGoesHere;"
http://www.connectionstrings.com/use-application-name-sql-server/
如果“应用程序名称”未在 .NET 连接字符串中设置,则使用 System.Data.SqlClient.SqlConnection
时的默认值为“.Net SqlClient Data Provider”。
其他一些常用于审计的字段:
以下是用于设置/获取上下文信息的 SQL 辅助方法:
CREATE PROC dbo.usp_ContextInfo_SET
@val varchar(128)
as
begin
set nocount on;
DECLARE @c varbinary(128);
SET @c=cast(@val as varbinary(128));
SET CONTEXT_INFO @c;
end
GO
CREATE FUNCTION [dbo].[ufn_ContextInfo_Get] ()
RETURNS varchar(128)
AS
BEGIN
--context_info is binary data type, so will pad any values will CHAR(0) to the end of 128 bytes, so need to replace these with empty string.
RETURN REPLACE(CAST(CONTEXT_INFO() AS varchar(128)), CHAR(0), '')
END
编辑:
app_name() 是获取查询中涉及的应用程序的首选方法,但是由于您不想更新任何 SSIS 包,因此这里是一个更新的查询,使用以下方法获取当前正在执行的作业记录的 SQL 代理表。您可能必须在 msdb 数据库中为这些表调整 SELECT 的 GRANT,以便查询成功,或者使用此查询创建 View ,然后调整该 View 的授权。
查询:
;with cteSessions as
(
--each time that SQL Agent is started, a new record is added to this table.
--The most recent session is the current session, and prior sessions can be used
--to identify the job state at the time that SQL Agent is restarted or stopped unexpectedly
select top 1 s.session_id
from msdb.dbo.syssessions s
order by s.agent_start_date desc
)
SELECT runningJobs =
STUFF(
( SELECT N', [' + j.name + N']'
FROM msdb.dbo.sysjobactivity a
inner join cteSessions s on s.session_id = a.session_id
inner join msdb.dbo.sysjobs j on a.job_id = j.job_id
left join msdb.dbo.sysjobhistory h2 on h2.instance_id = a.job_history_id
WHERE
--currently executing jobs:
h2.instance_id is null
AND a.start_execution_date is not null
AND a.stop_execution_date is null
ORDER BY j.name
FOR XML PATH(''), ROOT('root'), TYPE
).query('root').value('.', 'nvarchar(max)') --convert the xml to nvarchar(max)
, 1, 2, '') -- replace the leading comma and space with empty string.
;
编辑#2:
此外,如果您使用的是 SQL 2012 或更高版本,请查看 SSISDB.catalog.executions
View http://msdn.microsoft.com/en-us/library/ff878089(v=sql.110).aspx获取当前正在运行的 SSIS 包的列表,无论它们是否是从计划作业中启动的。我没有在 2012 年之前的 SQL Server 版本中看到等效 View 。
关于sql - 获取在 UPDATE 触发器上运行 SQL 查询的作业,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/23655913/
SQL、PL-SQL 和 T-SQL 之间有什么区别? 谁能解释一下这三者之间的区别,并提供每一个的相关使用场景? 最佳答案 SQL 是一种对集合进行操作的查询语言。 它或多或少是标准化的,几乎所有关
这个问题已经有答案了: What is the difference between SQL, PL-SQL and T-SQL? (6 个回答) 已关闭 9 年前。 我对 SQL 的了解足以完成我的
我在数据库中有一个 USER 表。该表有一个 RegistrationDate 列,该列有一个默认约束为 GETDATE()。 使用 LINQ 时,我没有为 RegistrationDate 列提供任
我有一个可能属于以下类型的字符串 string expected result 15-th-rp 15 15/12-rp 12 15-12-th
很难说出这里问的是什么。这个问题模棱两可、含糊不清、不完整、过于宽泛或言辞激烈,无法以目前的形式合理回答。如需帮助澄清此问题以便可以重新打开,visit the help center . 9年前关闭
我有一个存储过程(称为 sprocGetArticles),它从文章表中返回文章列表。这个存储过程没有任何参数。 用户可以对每篇文章发表评论,我将这些评论存储在由文章 ID 链接的评论表中。 有什么方
我目前正在做一个 *cough*Oracle*cough* 数据库主题。讲师介绍embedded SQL作为让其他语言(例如 C、C++)与(Oracle)数据库交互的方式。 我自己做了一些数据库工作
SQL Server 中 SQL 语句的最大长度是多少?这个长度是否取决于 SQL Server 的版本? 例如,在 DECLARE @SQLStatement NVARCHAR(MAX) = N'S
这个问题已经有答案了: Simple way to transpose columns and rows in SQL? (9 个回答) 已关闭 8 年前。 CallType
预先感谢您对此提供的任何帮助。 假设我有一个查询,可以比较跨年的数据,从某个任意年份开始,永无止境(进入 future ),每年同一时期直到最后一个完整的月份(其特点是一月数据永远不会显示至 2 月
我在数据库中有一个 USER 表。该表有一个 RegistrationDate 列,该列的默认约束为 GETDATE()。 使用 LINQ 时,我没有为 RegistrationDate 列提供任何数
下面是我试图用来检查存储过程是否不存在然后创建过程的 sql。它会抛出一个错误:Incorrect syntax near the keyword 'PROCEDURE' IF NOT EXISTS
我有一个同事声称动态 SQL 在许多情况下比静态 SQL 执行得更快,所以我经常看到 DSQL 到处都是。除了明显的缺点,比如在运行之前无法检测到错误并且更难阅读,这是否准确?当我问他为什么一直使用
来自 lobodava 的动态 SQL 查询是: declare @sql nvarchar(4000) = N';with cteColumnts (ORDINAL_POSITION, CO
使用 SQL Server 中的存储过程执行动态 SQL 命令的现实优点和缺点是什么 EXEC (@SQL) 对比 EXEC SP_EXECUTESQL @SQL ? 最佳答案 sp_executes
我有这个有效的 SQL 查询: select sum(dbos.Points) as Points, dboseasons.Year from dbo.StatLines dbos i
我正在调试一些构建成功运行的 SQL 命令的代码。 然而,在查询结束时,查询结果似乎被写入了一个文本文件。 完整的查询如下 echo SELECT DATE,DATETABLE,DATE,APPDAT
我有一些创建表的 .sql 文件(MS SQL 数据库): 表_1.sql: IF OBJECT_ID (N'my_schema.table1', N'U') IS NOT NULL DROP TAB
我写了下面的 SQL 存储过程,它一直给我错误@pid = SELECT MAX(... 整个过程是: Alter PROCEDURE insert_partyco @pname varchar(20
我在 SQL Server 2005 中有包含两列 Fruit 和 Color 的表,如下所示 Fruit Colour Apple Red Orange
我是一名优秀的程序员,十分优秀!