SQL2005+Identifying the Current SQL Server Login
Some queries and other operations should yield different results depending upon the user executing them. Sometimes this can be controlled by standard SQL Server security. When it cannot, it may be necessary to identify the current user from Transact-SQL.
SQL Logins
SQL Server supports two types of authorisation for connections. The first uses SQL Server authentication, where the login name and password are controlled by the database management system (DBMS). This type of authentication is no longer preferred by Microsoft; the recommendation is that Windows authentication should be used wherever possible. However, both systems of authentication are commonplace and can be used simultaneously for a single database.
When using either authentication system, you can add security information to your SQL Server instances and databases, limiting access to information and procedures according to the credentials supplied.
Sometimes it is necessary to obtain the login name of the current user. For example, you may wish to execute a query and filter the results depending upon the person making the request. Another common action is to record the name of the user when inserting or updating rows to provide a simple audit trail.
One way to identify the current user is with the SYSTEM_USER function. For connections made using SQL Server authentication, the function returns the name of the login as an NCHAR value. For example:
SELECT SYSTEM_USER
-- richard
For connections made using Windows authentication, the result contains a two-part identifier. If the user is an Active Directory domain user, it contains the domain name and user name. For other users, the computer name and user name are returned. For example:
SELECT SYSTEM_USER
-- BlackWasp\Richard
5 October 2015