HOW TO: Determine the User Name in a Data Access Page in an Access 2000 Project

You can determine the current network user name of a user who is logged on to a Microsoft Windows NT or a Microsoft Windows 2000 network by using Win32 API functions. However, because scripting languages such as VBScript or JScript do not support API calls for security purposes, you cannot use these functions in a data access page.

Microsoft SQL Server provides the SUSER_SNAME() function to return the user name that is logged on to SQL Server. By using this function in a stored procedure, you can determine the network user name in a data access page in an Access project. This article shows you how to do this.

To demonstrate how this procedure works, this article asks you to do the following:

• Create a SQL database and a table.
• Write a stored procedure to return the user name.
• Build the data access page with a script to filter records based on the return value from the stored procedure.

Creating the SQL Database and Table

1. Start Access 2000, click the Access database wizards, pages and projects option, and then click OK.
2. On the General tab, click Project (New Database), and then click OK.
3. Type a new file name for the project, and then click OK.
4. On the first page of the Microsoft SQL Server Database Wizard, select or type the name of a server to use to create the database.
5. Leave the user name and password blank. This will create the database using NT integrated security. You must have CREATE DATABASE permissions on the server to create a database on that server.
6. Type the name of a new SQL database to be created on the SQL Server.
7. Click Next, and then click Finish to create the new database.
8. In the Database window, click Stored Procedures, and then click New. Replace the existing code with the following stored procedure:

   Create Procedure CreateTestTable
   As
   CREATE TABLE TestTable (
   	CustomerID nchar(5) NOT NULL ,
   	CompanyName nvarchar(40) NOT NULL ,
   	ContactName nvarchar(30) NULL ,
   	ContactTitle nvarchar(30) NULL ,
   	Address nvarchar(60) NULL ,
   	City nvarchar(15) NULL ,
   	Region nvarchar(15) NULL ,
   	PostalCode nvarchar(10) NULL ,
   	Country nvarchar(15) NULL ,
   	UserName nvarchar(10) NULL 
   
   CONSTRAINT MyContraint PRIMARY KEY (CustomerID)
   
   )
   	return 
   						

   Close and save the stored procedure.
9. In the Database window, double-click the new stored procedure to run it. This creates a table on SQL Server. You must have CREATE TABLE permissions on the server to run this stored procedure.
10. After the table has been created, open it in Design View. Insert sample data into the new table, making sure to enter your network user name in the UserName field.

Writing a Stored Procedure to Return the User Name

1. In the Database window, click Stored Procedures, and then click New. Type the following stored procedure:

   Create Procedure spGetUserName
   As
   -- declare variables
   DECLARE @name varchar(30)
          ,@lenindex int
          ,@lenall int
          ,@username varchar(10)
   -- return the user logged into the SQL server
   SELECT @name = suser_sname()
   SELECT @lenall = Len(@name)
   SELECT @lenindex = CHARINDEX('\', @name)
   -- clean up the variables and return a field named NTUserName
   SELECT SUBSTRING(@name, @lenindex+1, @lenall-@lenindex) AS NTUserName
   	return
   						

   Close and save the stored procedure.
2. In the Database window, double-click the spGetUserName stored procedure to run it. Note that one field and one record is returned with your network user name. Close the results.

Create the Data Access Page

1. In the Database window, click Pages, and then click New. Click AutoPage: Columnar, select TestTable from the list of tables, and then click OK.
2. Open the page in Design view.
3. On the Tools menu, point to Macro, and then click Microsoft Script Editor.
4. Locate the Script Outline in the bottom-left corner of the screen.
5. Expand the Client Objects & Events folder, and expand the window object. Double-click the onload event. This will enter a client-side SCRIPT block for the event.
6. Type the following code:

   'Return the current user from the connection.
   'This will return "sa" when using SQL authentication, and an empty 
   'string if using NT authentication.  You must use a stored
   'procedure to parse out the NT user name using SQL functions
   
   dim rs, strUserName
   
   'This property will return SSPI when using NT integrated security
   if MSODSC.Connection.Properties("Integrated Security")="SSPI" then
      set rs=MSODSC.Connection.Execute("spGetUserName")
      strUserName=rs("NTUserName")
      rs.close: set rs=nothing
   
   'if the connection is using SQL authentication, use the user id from
   'the MSODSC connection
   else
      strUserName=MSODSC.Connection.Properties("User ID")
   end if
   
   'apply a serverfilter to the page for the current user name
   MSODSC.RecordsetDefs.item(0).ServerFilter="UserName='" & strUserName & "'"
   					

7. Close and save the data access page.
8. To test the page, open it in Microsoft Internet Explorer 5 or later. Note that you only see records where the UserName field contains your user name.

For information about how to determine the user name in a data access page in a Microsoft Access database (MDB), see the following article in the Microsoft Knowledge Base: