SUSER_SID

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

Syntax

SUSER_SID ( [ 'login' ] )

Arguments

'login'

Is the user's login name. login is sysname. login, which is optional, can be a Microsoft® SQL Server™ login or Microsoft Windows NT® user or group. If login is not specified, information about the current user is returned.

Return Types

varbinary(85)

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_SID 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_SID

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

SELECT SUSER_SID('sa')

B. Use SUSER_SID with a Windows NT username

This example returns the security identification number for the Windows NT user London\Workstation1.

SELECT SUSER_SID('London\Workstation1')

C. Use SUSER_SID as a DEFAULT constraint

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

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