SUSER_SNAME

Transact-SQL Reference

Transact-SQL Reference

SUSER_SNAME

Returns the login identification name from a user's security identification number (SID).

Syntax

SUSER_SNAME ( [ server_user_sid ] )

Arguments

server_user_sid

Is the user security identification number. server_user_sid, which is optional, is varbinary(85). server_user_sid can be the security identification number of any Microsoft® SQL Server™ login or Microsoft Windows NT® user or group. If server_user_sid is not specified, information about the current user is returned.

Return Types

nvarchar(256)

Remarks

When specifying a SQL Server login using SQL Server Authentication, the user must be granted permission to connect to SQL Server. Use sp_addlogin or SQL Server Enterprise Manager to grant this permission. However, when specifying a Windows NT user or group using Windows Authentication, this user or group does not have to be granted permission to connect to SQL Server.

SUSER_SNAME can be used as a DEFAULT constraint in either ALTER TABLE or CREATE TABLE.

System functions can be used in the select list, in the WHERE clause, and anywhere an expression is allowed, and must always be followed by parentheses (even if no parameter is specified).

Examples
A. Use SUSER_SNAME

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

SELECT SUSER_SNAME(0x01)
B. Use SUSER_SNAME with a Windows NT user's security identification number

This example returns the login name for the Windows NT user's security identification number, obtained by using SUSER_SID.

SELECT SUSER_SNAME(0x010500000000000515000000a065cf7e784b9b5fe77c87705a2e0000)
C. Use SUSER_SNAME as a DEFAULT constraint

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

USE pubs
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

See Also

ALTER TABLE

binary and varbinary

CREATE TABLE

Managing Security

sp_addlogin

sp_grantlogin

System Functions