Note
Access to this page requires authorization. You can try signing in or changing directories.
Access to this page requires authorization. You can try changing directories.
Applies to:
SQL Server
Azure SQL Database
Azure SQL Managed Instance
SQL database in Microsoft Fabric Preview
Returns the security identification number (SID) for the specified login name.
Transact-SQL syntax conventions
Syntax
SUSER_SID ( [ 'login' ] [ , Param2 ] )
Arguments
'* login *'
Applies to: SQL Server 2008 (10.0.x) and later versions
The login name of the user. login is sysname. login, which is optional, can be a SQL Server login or Microsoft Windows user or group. If login is not specified, information about the current security context is returned. If the parameter contains NULL
, SUSER_SID
returns NULL
.
Param2
Applies to: SQL Server 2012 (11.x) and later versions
Specifies whether the login name is validated. Param2 is of type int and is optional. When Param2 is 0, the login name is not validated. When Param2 is not specified as 0, the Windows login name is verified to be exactly the same as the login name stored in SQL Server.
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.
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. Differing collations is often a cause of SQL Server error 15401: Windows NT user or group '%s' not found. Check the name again.
Remarks for Azure SQL Database, SQL database in Fabric
SUSER_SID
always return the login SID for the current security context. Use sys.database_principals to obtain the SID of a different login.
The SUSER_SID
statement does not support execution using an impersonated security context through EXECUTE AS
.
Examples
A. Use SUSER_SID
The following example returns the security identification number (SID) for the current security context.
SELECT SUSER_SID();
B. Use SUSER_SID with a specific login
Applies to: SQL Server 2012 (11.x) and later versions
The following example returns the security identification number for the SQL Server sa
login.
SELECT SUSER_SID('sa');
GO
C. Use SUSER_SID with a Windows user name
Applies to: SQL Server 2012 (11.x) and later versions
The following example returns the security identification number for the Windows user London\Workstation1
.
SELECT SUSER_SID('London\Workstation1');
GO
D. Use SUSER_SID as a DEFAULT constraint
The following example uses SUSER_SID
as a DEFAULT
constraint in a CREATE TABLE
statement.
USE AdventureWorks2022;
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
E. Compare the Windows login name to the login name stored in SQL Server
Applies to: SQL Server 2012 (11.x) and later versions
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));