SUSER_SID (Transact-SQL)

Returns the security identification number (SID) for the specified login name.

Transact-SQL Syntax Conventions

Syntax

SUSER_SID ( [ 'login' ] ) 

Arguments

• 'login'
  Is the login name of the user. login is sysname. login, which is optional, can be a Microsoft SQL Server login or Microsoft Windows user or group. If login is not specified, information about the current security context is returned.

Return Types

varbinary(85)

Remarks

SUSER_SID can be used as a DEFAULT constraint in either ALTER TABLE or CREATE TABLE. SUSER_SID can be used in a select list, in a WHERE clause, and anywhere an expression is allowed. SUSER_SID must always be followed by parentheses, even if no parameter is specified.

When called without an argument, SUSER_SID returns the SID of the current security context. When called without an argument within a batch that has switched context by using EXECUTE AS, SUSER_SID returns the SID of the impersonated context. When called from an impersonated context, SUSER_SID(ORIGINAL_LOGIN()) returns the SID of the original context.

Examples

A. Using SUSER_SID

The following example returns the security identification number for the SQL Server sa login.

SELECT SUSER_SID('sa');
GO

B. Using SUSER_SID with a Windows user name

The following example returns the security identification number for the Windows user London\Workstation1.

SELECT SUSER_SID('London\Workstation1');
GO

C. Using SUSER_SID as a DEFAULT constraint

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

USE AdventureWorks;
GO
CREATE TABLE sid_example
(
login_sid   varbinary(85) DEFAULT SUSER_SID(),
login_name  varchar(30) DEFAULT SYSTEM_USER,
login_dept  varchar(10) DEFAULT 'SALES',
login_date  datetime DEFAULT GETDATE()
) 
GO
INSERT sid_example DEFAULT VALUES
GO

See Also

Reference

ORIGINAL_LOGIN (Transact-SQL)
CREATE TABLE (Transact-SQL)
binary and varbinary (Transact-SQL)
System Functions (Transact-SQL)

Help and Information

Getting SQL Server 2005 Assistance