Sql-server – Microsoft SQL Server Error: 18456(Administrator is also not able to access)

This is expected. LUA strips administrators membership from your token. From Getting Started with User Account Control on Windows Vista:

To help prevent malware silent installation and computer-wide infection, Microsoft developed the UAC feature for Windows Vista. Unlike previous versions of Windows, when an administrator logs on to a Windows Vista computer, the user’s full administrator access token is split into two access tokens: a full administrator access token and a standard user access token. During the logon process, authorization and access control components that identify an administrator are removed or disabled, resulting in a standard user access token. The standard user access token is then used to launch the desktop, the Explorer.exe process. Because all applications inherit their access control data from the initial launch of the desktop, they all run as a standard user as well. Contrasting with this process, when a standard user logs on, only a standard user access token is created. This standard user access token is then used to launch the desktop.

Since your local desktop token has the administrator membership removed, it cannot log in locally to SQL Server if you expect to log in through builtin\Administrators. On remote access your token preserves the Administrator membership and you gain access.

The solution is to grant SQL Server login rights through an explicit domain group account and add all users that require access to your SQL instance to this domain group. Do not rely on Administrators membership, is a bad practice.

I fixed this by 1) removing the WITH EXECUTE AS 'user1' clause from the stored procedure and 2) removing user1 from the job -- it is no longer the owner or the "Run as user" of the job's step.

My final configuration is:

• SQL Server Agent job owner is a sysadmin, not user1
• The job's step "Run as user" is left blank
• The step calls a stored procedure which user1 has rights to execute
• The linked server has a local/remote mapping for user1 with the remote user and remote password entered. The username and password are the same on both servers. The "For a login not defined in the list above, connections will" option is set to "Be made using the login's current security context".
• user1 has the appropriate permissions to query on the remote server.
• user1 does not need to have the sysadmin role or any such administrative role