Returns the security identification number (SID) for the specified login name.
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.
When the SQL Server collation and the Windows collation are different, SUSER_SID can fail when SQL Server and Windows store the login in a different format. For example, if the Windows computer TestComputer has the login User and SQL Server stores the login as TESTCOMPUTER\User, the lookup of the login TestComputer\User might fail to resolve the login name correctly. To skip this validation of the login name, use Param2.
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
D. Comparing the Windows login name to the login name stored in SQL Server
The following example shows how to use Param2 to obtain the SID from Windows and uses that SID as an input to the SUSER_SNAME function. The example provides the login in the format in which it is stored in Windows (TestComputer\User), and returns the login in the format in which it is stored in SQL Server (TESTCOMPUTER\User).
SELECT SUSER_SNAME(SUSER_SID('TestComputer\User', 0)) ;