Excel与SQL Server通信以识别用户。

Excel queries SQL Server. Trying to query using current user name. I have an Excel file that queries a view in SQL Server. The view is set to return the results based on the user querying the view.

Excel查询SQL Server。尝试使用当前用户名进行查询。我有一个Excel文件,用于查询SQL Server中的视图。视图设置为根据查询视图的用户返回结果。

I setup a file on a network server for others to access, hoping that when they opened the file, it would show only the information relating to them.

我在网络服务器上设置了一个文件供其他人访问,希望当他们打开文件时,它只会显示与它们相关的信息。

But the information still only refers to myself. I want it to show information the user using the file, as if they were querying the view directly.

但信息仍然只是指我自己。我希望它显示用户使用该文件的信息,就像他们直接查询视图一样。

Here is the sample query for the view:

以下是视图的示例查询:

Select * from dbo.WorkEstimate where Estimator = SUSER_NAME()

But the Excel sheet only returns the information relating to myself. I think it may have something to do with the connectstring that always specifies my workstation name. Is there a way to change that to reflect the individual (or their workstation) that is using the file?

但Excel工作表仅返回与我自己相关的信息。我认为它可能与连接字符串有关,它始终指定我的工作站名称。有没有办法改变它以反映使用该文件的个人(或他们的工作站)?

2 个解决方案

#1

The SUSER_NAME() (transact-sql) in the SQL statment refers to the current user within the context of the currently executing SQL statement. In your case, it is the userID you specified in your connection string. The sql server uses that user specified in your connectionstring to run that statement and thus your SUSER_NAME() reflect that user.

SQL语句中的SUSER_NAME()(transact-sql)引用当前正在执行的SQL语句的上下文中的当前用户。在您的情况下,它是您在连接字符串中指定的用户标识。 sql server使用您的connectionstring中指定的用户来运行该语句,因此您的SUSER_NAME()反映了该用户。

You will need to specify the user that the Excel is running under (Windows environment) with the following Excel functions:

您需要使用以下Excel函数指定运行Excel的用户(Windows环境):

Application.Username is the name of the User set in Excel Tools>Options (under the User Name box)

Application.Username是Excel工具>选项中设置的用户名(在“用户名”框下)

Environ("Username") is the name you registered for Windows see Control Panel >System

Environ(“用户名”)是您为Windows注册的名称,请参阅控制面板>系统

So, your SQL statement will have have to be something like:

所以,你的SQL语句必须是这样的:

vsSQL = "SELECT * FROM dbo.WorkEstimate WHERE (Estimator = '" & Environ("Username") & "');"

Hope this helps.

希望这可以帮助。

#2

You don't need to have Excel communicate with SQL Server to figure that out. Excel needs to communicate with the user's machine.

您不需要让Excel与SQL Server通信就可以解决这个问题。 Excel需要与用户的计算机进行通信。

Use this code to discover all of the environment variables that pertain to the user logged in to the machine. Look through the results to figure out what you need to pass in to your query.

使用此代码可以发现与登录到计算机的用户相关的所有环境变量。查看结果以确定您需要传递给查询的内容。

Add a module to your Excel file and paste in the following:

将模块添加到Excel文件并粘贴以下内容:

Sub ListAllEnvironmentVariables()
    Dim ws As Worksheet
    Dim rng As Range

    Set ws = Sheets("Sheet1")
    Set rng = ws.Range("A1")

    Dim i As Integer
    i = 1
    Do Until Environ$(i) = ""
        rng.Offset(i - 1).Value = Environ(i)
        i = i + 1
    Loop
End Sub

I'm using Sheet1 in my example, but you can change that and set ws to any blank sheet in your workbook to see how it works. Obviously don't use Sheet1 if you have data on the sheet.

我在我的示例中使用了Sheet1,但您可以更改它并将ws设置为工作簿中的任何空白工作表以查看其工作原理。如果工作表上有数据,显然不要使用Sheet1。

#1