USER_NAME (Transact-SQL)
Applies to:
SQL Server
Azure SQL Database
Azure SQL Managed Instance
Azure Synapse Analytics
Analytics Platform System (PDW)
SQL analytics endpoint in Microsoft Fabric
Warehouse in Microsoft Fabric
Returns a database user name from a specified identification number, or the current user name.
Transact-SQL syntax conventions
Syntax
USER_NAME ( [ ID ] )
Note
To view Transact-SQL syntax for SQL Server 2014 (12.x) and earlier versions, see Previous versions documentation.
Arguments
ID
The identification number associated with a database user, as listed in sys.database_principals. ID is int. The parentheses are required.
Return types
nvarchar(128)
Remarks
When ID is omitted, the current user in the current context is assumed. If the parameter contains the word NULL
, USER_NAME
will return NULL
. When USER_NAME
is called without specifying an ID after an EXECUTE AS
statement, USER_NAME
returns the name of the impersonated user. If a Windows principal accesses the database by way of membership in a group, USER_NAME
returns the name of the Windows principal instead of the group.
Although the USER_NAME()
function is supported on Azure SQL Database, using EXECUTE AS USER = USER_NAME(n)
is not supported on Azure SQL Database.
Examples
A. Use USER_NAME() to identify a user ID
The following example returns the user name for user ID 13
, as listed in sys.database_principals.
SELECT USER_NAME(13);
GO
B. Use USER_NAME without an ID
The following example finds the name of the current user without specifying an ID.
SELECT USER_NAME();
GO
Here is the result set for a user that is a member of the sysadmin fixed server role.
dbo
C. Use USER_NAME in the WHERE clause
The following example finds the row in sys.database_principals
, in which the name is equal to the result of applying the system function USER_NAME
to user identification number 1
.
SELECT name FROM sys.database_principals WHERE name = USER_NAME(1);
GO
Here is the result set.
name
------------------------------
dbo
(1 row(s) affected)
D. Call USER_NAME during impersonation with EXECUTE AS
The following example shows how USER_NAME
behaves during impersonation.
EXECUTE AS
is not currently supported on Microsoft Fabric.
Caution
When testing with EXECUTE AS
, always script a REVERT
to follow.
SELECT USER_NAME();
GO
EXECUTE AS USER = 'Zelig';
GO
SELECT USER_NAME();
GO
REVERT;
GO
SELECT USER_NAME();
GO
Here is the result set.
-------------
dbo
-------------
Zelig
-------------
dbo
Examples: Azure Synapse Analytics and Analytics Platform System (PDW)
E. Use USER_NAME without an ID
The following example finds the name of the current user without specifying an ID.
SELECT USER_NAME();
Here is the result set for a currently logged-in user.
User7
F. Use USER_NAME in the WHERE clause
The following example finds the row in sysusers
in which the name is equal to the result of applying the system function USER_NAME
to user identification number 1
.
SELECT name FROM sysusers WHERE name = USER_NAME(1);
Here is the result set.
name
------------------------------
User7