I'm in the process of creating a simple ASP.NET Details and Grid View in two separate pages for simple record keeping. The Details View will be leveraged to enter records into the database and the Grid View will be leveraged for any updating of these records. I should note that I have only 'Windows Authentication' enabled on my IIS server.
我正在两个单独的页面中创建一个简单的ASP.NET详细信息和网格视图，以保持简单的记录。将利用详细信息视图将记录输入数据库，并且将利用网格视图来更新这些记录。我应该注意，我的IIS服务器上只启用了“Windows身份验证”。
One of the features I'm adding is audting when records are inserted or updated into the database. I'm using the CURRENT_TIMESTAMP command in SQL Server 2012 to enter when records are inserted and/or updated. However, I'm attempting to write which active directory user updated or inserted a given record. I first attempted to leverage the SYSTEM_USER command but all that writes to the database is the SQL server service acccount or dbo account which is unacceptable. I understand why the SQL Server service account is being written but what I need is to have the current Active Directory user's name be written to the 'username' field as noted below.
我添加的功能之一是在将记录插入或更新到数据库时进行审核。我正在使用SQL Server 2012中的CURRENT_TIMESTAMP命令输入插入和/或更新记录的时间。但是，我正在尝试编写哪个活动目录用户更新或插入给定记录。我首先尝试利用SYSTEM_USER命令，但写入数据库的所有内容都是SQL服务器帐户或dbo帐户，这是不可接受的。我理解为什么要编写SQL Server服务帐户，但我需要的是将当前的Active Directory用户名写入“用户名”字段，如下所述。
Here's the commands I'm using right now for the timestamp and username (this isn't the exact SQL command I'm using):
这是我正在使用的命令，用于时间戳和用户名（这不是我正在使用的确切SQL命令）：
INSERT INTO <table-name> ([timestamp], [username]...) VALUES (CURRENT_TIMESTAMP, SYSTEM_USER...)
UPDATE <table-name> SET [timestamp] = CURRENT_TIMESTAMP, [username]) = SYSTEM_USER WHERE.....
What is the best way to get the current Active Directory user's name written to the 'username' field whenever they insert and/or update a given record and not the SQL Server Service account's credentials?
每当插入和/或更新给定记录而不是SQL Server服务帐户的凭据时，将当前Active Directory用户的名称写入“用户名”字段的最佳方法是什么？
Thanks in advance!
提前致谢！
2 个解决方案
#1
2
How are you connecting to SQL Server? Are you connecting using SQL Authentication or using Windows Authentication?
你是如何连接到SQL Server的？您是使用SQL身份验证连接还是使用Windows身份验证？
If using sql auth, then there isn't a way without passing that information along with the database command (query).
如果使用sql auth，那么没有办法将这些信息与数据库命令（查询）一起传递。
If you are using Windows Authentication, are you impersonating the user in ASP.NET? If not, then it's the same problem as sql authentication.
如果您使用的是Windows身份验证，是否在ASP.NET中模拟用户？如果没有，那么它与sql身份验证的问题相同。
If you are using impersonation in ASP.NET, then SYSTEM_USER should get you what you need.
如果您在ASP.NET中使用模拟，那么SYSTEM_USER应该可以满足您的需求。
If your website is using forms authentication a user store other than AD, then you'll have the same problem as sql auth.
如果您的网站使用表单身份验证而不是AD的用户存储，那么您将遇到与sql身份验证相同的问题。
Think of it this way: Is SQL Server aware of the user of the website? In most cases, no, unless you are using impersonation with windows auth or you are telling SQL Server the user name (i.e. passing it in with your command statement).
可以这样想：SQL Server是否知道该网站的用户？在大多数情况下，不，除非您使用Windows身份验证模拟，或者您正在告诉SQL Server用户名（即使用您的命令语句传递它）。
#2
0
You could store the application username on the context_info variable. And afterwards retrieve it in your trigger or anywhere you need it.
您可以将应用程序用户名存储在context_info变量中。然后在您的触发器或任何您需要的地方检索它。
You should do this before each connection to sql is made; should be easy to do, since usually you have the code for the connection in one place only.
你应该在每次连接到sql之前执行此操作;应该很容易做到，因为通常只在一个地方有连接代码。
#1
2
How are you connecting to SQL Server? Are you connecting using SQL Authentication or using Windows Authentication?
你是如何连接到SQL Server的？您是使用SQL身份验证连接还是使用Windows身份验证？
If using sql auth, then there isn't a way without passing that information along with the database command (query).
如果使用sql auth，那么没有办法将这些信息与数据库命令（查询）一起传递。
If you are using Windows Authentication, are you impersonating the user in ASP.NET? If not, then it's the same problem as sql authentication.
如果您使用的是Windows身份验证，是否在ASP.NET中模拟用户？如果没有，那么它与sql身份验证的问题相同。
If you are using impersonation in ASP.NET, then SYSTEM_USER should get you what you need.
如果您在ASP.NET中使用模拟，那么SYSTEM_USER应该可以满足您的需求。
If your website is using forms authentication a user store other than AD, then you'll have the same problem as sql auth.
如果您的网站使用表单身份验证而不是AD的用户存储，那么您将遇到与sql身份验证相同的问题。
Think of it this way: Is SQL Server aware of the user of the website? In most cases, no, unless you are using impersonation with windows auth or you are telling SQL Server the user name (i.e. passing it in with your command statement).
可以这样想：SQL Server是否知道该网站的用户？在大多数情况下，不，除非您使用Windows身份验证模拟，或者您正在告诉SQL Server用户名（即使用您的命令语句传递它）。
#2
0
You could store the application username on the context_info variable. And afterwards retrieve it in your trigger or anywhere you need it.
您可以将应用程序用户名存储在context_info变量中。然后在您的触发器或任何您需要的地方检索它。
You should do this before each connection to sql is made; should be easy to do, since usually you have the code for the connection in one place only.
你应该在每次连接到sql之前执行此操作;应该很容易做到，因为通常只在一个地方有连接代码。