SQL Server:修改“应用程序名称"用于审计目的的财产
由于我们没有将应用程序的用户实现为 SQL Server 中的用户，因此当应用程序服务器连接到数据库时，每个应用程序始终使用相同的凭据附加到每个数据库.
As we do not implement the users of our applications as users in SQL server, when the application server connects to a database each application always uses the same credentials to attach to each database.
这会带来审计问题.使用触发器，我们希望存储每个更新、插入和删除并将每个属性都归于特定用户.一种可能的解决方案是向每个表添加一个由用户更新"列，并每次都进行更新.这意味着每个表都有一个新列，每个存储过程都有一个新参数.这也意味着您只能进行软删除.
This presents an auditing problem. Using triggers, we want to store every update, insert and delete and attribute each to a particular user. One possible solution is to add an "updated by user" column to every table and update this every time. This means a new column on every table and a new parameter on every stored procedure. It also means you can only do soft deletes.
我建议使用连接字符串的 Application Name 属性并使用触发器内的 App_Name() 属性读取它，而不是这个.我用一个简单的应用程序对此进行了测试，它似乎可以工作(格式可能是这样的:App=MyApp|User=100).
Instead of this I propose using the Application Name property of the connection string and reading this with the App_Name() property inside the trigger. I tested this with a simple app and it seems to work (the format could be as so: App=MyApp|User=100).
你们的问题是，这是一个坏主意吗?你们有更好的主意吗?
The question for you guys is, is this a bad idea and do you have a better one?
推荐答案
我使用 为此设置 CONTEXT_INFO.这正是您所需要的.
I use SET CONTEXT_INFO for this. It's just what you need.
相关文章