SUSER_SID (Transact-SQL)SUSER_SID (Transact-SQL)

适用于: 是SQL Server 是Azure SQL 数据库 否Azure Synapse Analytics (SQL DW) 否并行数据仓库 APPLIES TO: YesSQL Server YesAzure SQL Database NoAzure Synapse Analytics (SQL DW) NoParallel Data Warehouse

返回指定登录名的安全标识号 (SID)。Returns the security identification number (SID) for the specified login name.

主题链接图标 Transact-SQL 语法约定Topic link icon Transact-SQL Syntax Conventions

语法Syntax

  
SUSER_SID ( [ 'login' ] [ , Param2 ] )   

参数Arguments

' login '' login '
适用于SQL Server 2008SQL Server 2008 及更高版本Applies to: SQL Server 2008SQL Server 2008 and later

用户的登录名。Is the login name of the user. login 为 sysname 。login is sysname. login 作为可选项,可以为 SQL ServerSQL Server 登录名或 MicrosoftMicrosoft Windows 用户或组 。login, which is optional, can be a SQL ServerSQL Server login or MicrosoftMicrosoft Windows user or group. 如果未指定 login,则返回有关当前安全上下文的信息 。If login is not specified, information about the current security context is returned. 如果此参数包含词 NULL,将返回 NULL。If the parameter contains the word NULL will return NULL.

Param2 Param2
适用于SQL Server 2012 (11.x)SQL Server 2012 (11.x) 及更高版本Applies to: SQL Server 2012 (11.x)SQL Server 2012 (11.x) and later

指定是否验证登录名。Specifies whether the login name is validated. Param2 的类型为 int,并且可选 。Param2 is of type int and is optional. 在 Param2 为 0 时,不验证登录名 。When Param2 is 0, the login name is not validated. 在 Param2 未指定为 0 时,对 Windows 登录名进行验证,以便确认是否与在 SQL ServerSQL Server 中存储的登录名完全相同 。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 ServerSQL Server.

返回类型Return Types

varbinary(85)varbinary(85)

备注Remarks

SUSER_SID 在 ALTER TABLE 或 CREATE TABLE 中可用作 DEFAULT 约束。SUSER_SID can be used as a DEFAULT constraint in either ALTER TABLE or CREATE TABLE. SUSER_SID 可以在选择列表、WHERE 子句和任何允许使用表达式的地方使用。SUSER_SID can be used in a select list, in a WHERE clause, and anywhere an expression is allowed. SUSER_SID 必须始终后跟括号,即使在未指定参数的情况下也是如此。SUSER_SID must always be followed by parentheses, even if no parameter is specified.

在无参数的情况下调用时,SUSER_SID 将返回当前安全上下文的 SID。When called without an argument, SUSER_SID returns the SID of the current security context. 当通过使用 EXECUTE AS 切换上下文的批处理中无参数调用时,SUSER_SID 将返回模拟上下文的 SID。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. 从模拟上下文中调用时,SUSER_SID(ORIGINAL_LOGIN()) 将返回原始上下文的 SID。When called from an impersonated context, SUSER_SID(ORIGINAL_LOGIN()) returns the SID of the original context.

SQL ServerSQL Server 排序规则和 Windows 排序规则不同时,如果 SQL ServerSQL Server 和 Windows 以不同格式存储登录名,SUSER_SID 可能会失败。When the SQL ServerSQL Server collation and the Windows collation are different, SUSER_SID can fail when SQL ServerSQL Server and Windows store the login in a different format. 例如,如果 Windows 计算机 TestComputer 具有登录名 User,而 SQL ServerSQL Server 将该登录名存储为 TESTCOMPUTER\User,则查找登录名 TestComputer\User 可能无法正确解析该登录名。For example, if the Windows computer TestComputer has the login User and SQL ServerSQL Server stores the login as TESTCOMPUTER\User, the lookup of the login TestComputer\User might fail to resolve the login name correctly. 若要跳过此登录名的验证,请使用 Param2 。To skip this validation of the login name, use Param2. 排序规则不同通常是 SQL ServerSQL Server 错误 15401 的原因:Differing collations is often a cause of SQL ServerSQL Server error 15401:

Windows NT user or group '%s' not found. Check the name again.

Azure SQL 数据库Azure SQL Database 备注Remarks

SUSER_SID 始终返回当前安全上下文的登录 ID。SUSER_SID always return the login SID for the current security context. 使用 sys.database_principals 获取不同登录名的 SID。Use sys.database_principals to obtain the SID of a different login.

SUSER_SID 语句不支持通过 EXECUTE AS 使用模拟安全上下文执行。The SUSER_SID statement does not support execution using an impersonated security context through EXECUTE AS.

示例Examples

A.A. 使用 SUSER_SIDUsing SUSER_SID

下面的示例返回当前安全上下文的安全标识号 (SID)。The following example returns the security identification number (SID) for the current security context.

SELECT SUSER_SID();  

B.B. 将 SUSER_SID 用于特定的登录名Using SUSER_SID with a specific login

下面的示例返回 SQL ServerSQL Server sa 登录名的安全标识号。The following example returns the security identification number for the SQL ServerSQL Server sa login.

适用于SQL Server 2012 (11.x)SQL Server 2012 (11.x) 及更高版本Applies to: SQL Server 2012 (11.x)SQL Server 2012 (11.x) and later

SELECT SUSER_SID('sa');  
GO  

C.C. 对 Windows 用户名使用 SUSER_SIDUsing SUSER_SID with a Windows user name

以下示例返回 Windows 用户 London\Workstation1 的安全标识号。The following example returns the security identification number for the Windows user London\Workstation1.

适用于SQL Server 2012 (11.x)SQL Server 2012 (11.x) 及更高版本Applies to: SQL Server 2012 (11.x)SQL Server 2012 (11.x) and later

SELECT SUSER_SID('London\Workstation1');  
GO  

D.D. 将 SUSER_SID 用作 DEFAULT 约束Using SUSER_SID as a DEFAULT constraint

下面的示例在 SUSER_SID 语句中使用 DEFAULT 作为 CREATE TABLE 约束。The following example uses SUSER_SID as a DEFAULT constraint in a CREATE TABLE statement.

USE AdventureWorks2012;  
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.E. 将 Windows 登录名与在 SQL Server 中存储的登录名进行比较Comparing the Windows login name to the login name stored in SQL Server

下面的示例演示如何使用 Param2 从 Windows 获取 SID 并使用该 SID 作为对 SUSER_SNAME 函数的输入 。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. 该示例以在 Windows 中存储的格式 (TestComputer\User) 提供登录名,并且以在 SQL ServerSQL Server (TESTCOMPUTER\User) 中存储的格式返回登录名。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 ServerSQL Server (TESTCOMPUTER\User).

适用于SQL Server 2012 (11.x)SQL Server 2012 (11.x) 及更高版本Applies to: SQL Server 2012 (11.x)SQL Server 2012 (11.x) and later

SELECT SUSER_SNAME(SUSER_SID('TestComputer\User', 0));  

另请参阅See Also

ORIGINAL_LOGIN (Transact-SQL) ORIGINAL_LOGIN (Transact-SQL)
CREATE TABLE (Transact-SQL) CREATE TABLE (Transact-SQL)
binary 和 varbinary (Transact-SQL) binary and varbinary (Transact-SQL)
系统函数 (Transact-SQL)System Functions (Transact-SQL)