SUSER_SNAME (Transact-SQL)

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
  Is the login security identification number. server_user_sid, which is optional, 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.

Return Types

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.

Examples

A. Using SUSER_SNAME

The following example returns the login name for the security identification number with a value of 0x01.

SELECT SUSER_SNAME(0x01);
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.

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 AdventureWorks;
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.

SELECT SUSER_SNAME();

GO

EXECUTE AS LOGIN = 'WanidaBenShoof';

SELECT SUSER_SNAME();

REVERT;

GO

SELECT SUSER_SNAME();

GO

Here is the result.

sa

WanidaBenShoof

sa

See Also

Reference

SUSER_SID (Transact-SQL)
sys.server_principals (Transact-SQL)

Other Resources

Principals

Help and Information

Getting SQL Server 2005 Assistance