SUSER_SNAME (Transact-SQL)

Article
2024-09-03

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 the login name associated with a security identification number (SID).

Transact-SQL syntax conventions

Syntax

SUSER_SNAME ( [ server_user_sid ] )

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. Refer to the sid column in sys.server_principals or sys.sql_logins catalog views. If server_user_sid isn't specified, information about the current user is returned. If the parameter contains the word NULL, SUSER_SNAME returns 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

The following example returns the login name of the current login.

SELECT SUSER_SNAME() AS CurrentLogin;
GO

Share via