Was this page helpful?
Your feedback about this content is important. Let us know what you think.
Additional feedback?
1500 characters remaining
Export (0) Print
Expand All

SUSER_SNAME (Transact-SQL)

Applies To: SQL Server 2014, SQL Server 2016 Preview

Topic Status: Some information in this topic is preview and subject to change in future releases. Preview information describes new features or changes to existing features in Microsoft SQL Server 2016 Community Technology Preview 2 (CTP2).

Returns the login name associated with a security identification number (SID).

Applies to: SQL Server (SQL Server 2008 through current version), Azure SQL Database.

Topic link icon Transact-SQL Syntax Conventions

SUSER_SNAME ( [ server_user_sid ] ) 

server_user_sid

Applies to: SQL Server 2008 through SQL Server 2016.

Is 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 is not specified, information about the current user is returned. If the parameter contains the word NULL will return NULL.

nvarchar(128)

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_NAME always return the login name for the current security context.

The SUSER_SNAME statement does not support execution using an impersonated security context through EXECUTE AS.

A. Using SUSER_SNAME

The following example returns the login name for the current security context.

SELECT SUSER_SNAME();
GO

B. Using SUSER_SNAME with a Windows user security ID

The following example returns the login name associated with a Windows security identification number.

Applies to: SQL Server 2008 through SQL Server 2016.

SELECT SUSER_SNAME(0x010500000000000515000000a065cf7e784b9b5fe77c87705a2e0000);
GO

C. Using SUSER_SNAME as a DEFAULT constraint

The following example uses SUSER_SNAME as a DEFAULT constraint in a CREATE TABLE statement.

USE AdventureWorks2012;
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. Calling SUSER_SNAME in combination with EXECUTE AS

This example shows the behavior of SUSER_SNAME when called from an impersonated context.

Applies to: SQL Server 2008 through SQL Server 2016.

SELECT SUSER_SNAME();
GO
EXECUTE AS LOGIN = 'WanidaBenShoof';
SELECT SUSER_SNAME();
REVERT;
GO
SELECT SUSER_SNAME();
GO

Here is the result.

sa

WanidaBenShoof

sa

Community Additions

ADD
Show:
© 2015 Microsoft