Returns the login name associated with a security identification number (SID).
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.
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 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.
SELECT SUSER_SNAME(); GO EXECUTE AS LOGIN = 'WanidaBenShoof'; SELECT SUSER_SNAME(); REVERT; GO SELECT SUSER_SNAME(); GO
Here is the result.