SUSER_SNAME (Transact-SQL)
Applies to:
SQL Server
Azure SQL Database
Azure SQL Managed Instance
Azure Synapse Analytics
Analytics Platform System (PDW)
Returns the login name associated with a security identification number (SID).
Transact-SQL syntax conventions
Syntax
SUSER_SNAME ( [ server_user_sid ] )
Note
To view Transact-SQL syntax for SQL Server 2014 and earlier, see Previous versions documentation.
Arguments
server_user_sid
The optional login security identification number. server_user_sid is varbinary(85). server_user_sid can be the security identification number of any SQL Server login or Microsoft Windows user or group. If server_user_sid isn't specified, information about the current user is returned. If the parameter contains the word NULL will return NULL.
server_user_sid is not supported on Azure SQL Database.
Return type
nvarchar(128)
Remarks
SUSER_SNAME
can be used as a DEFAULT constraint in either ALTER TABLE
or CREATE TABLE
. SUSER_SNAME
can be used in a select list, in a WHERE clause, and anywhere an expression is allowed. SUSER_SNAME
must always be followed by parentheses, even if no parameter is specified.
When called without an argument, SUSER_SNAME
returns the name of the current security context. When called without an argument within a batch that has switched context by using EXECUTE AS, SUSER_SNAME
returns the name of the impersonated context. When called from an impersonated context, ORIGINAL_LOGIN
returns the name of the original context.
Azure SQL Database remarks
SUSER_SNAME
always returns the login name for the current security context.
The SUSER_SNAME
statement doesn't support execution using an impersonated security context through EXECUTE AS.
SUSER_SNAME
doesn't support the server_user_id argument.
Examples
A. Use SUSER_SNAME
The following example returns the login name for the current security context.
SELECT SUSER_SNAME();
GO
B. Use SUSER_SNAME with a Windows user security ID
The following example returns the login name associated with a Windows security identification number.
SELECT SUSER_SNAME(0x010500000000000515000000a065cf7e784b9b5fe77c87705a2e0000);
GO
C. Use SUSER_SNAME as a DEFAULT constraint
The following example uses SUSER_SNAME
as a DEFAULT constraint in a CREATE TABLE
statement.
USE AdventureWorks2022;
GO
CREATE TABLE sname_example (
login_sname SYSNAME DEFAULT SUSER_SNAME(),
employee_id UNIQUEIDENTIFIER DEFAULT NEWID(),
login_date DATETIME DEFAULT GETDATE()
);
GO
INSERT sname_example DEFAULT
VALUES;
GO
D. Call SUSER_SNAME in combination with EXECUTE AS
This example shows the behavior of SUSER_SNAME
when called from an impersonated context.
SELECT SUSER_SNAME();
GO
EXECUTE AS LOGIN = 'WanidaBenShoof';
SELECT SUSER_SNAME();
REVERT;
GO
SELECT SUSER_SNAME();
GO
Here is the result.
sa
WanidaBenShoof
sa
Examples: Azure Synapse Analytics and Analytics Platform System (PDW)
E. Use SUSER_SNAME
The following example returns the login name for the security identification number with a value of 0x01
.
SELECT SUSER_SNAME(0x01);
GO
F. Return the current login
The following example returns the login name of the current login.
SELECT SUSER_SNAME() AS CurrentLogin;
GO