Functions That Return Usernames and User IDs

Several system functions return usernames and user IDs. Understanding the parameters and output of these functions requires understanding the types of names and IDs used in Microsoft® SQL Server™.

Each user logging in to SQL Server has two levels of names in SQL Server, and each name is associated with a unique ID:

• Login names

  Each user authorized to log in to SQL Server has one login name that gives them access to a SQL Server installation. There are two types of login names:

  • Microsoft Windows NT® account names

    Members of the sysadmin or securityadmin fixed server roles can authorize the Windows NT accounts of individual users or Windows NT groups to log in to a SQL Server installation using sp_grantlogin. The user identified by the Windows NT account, or any person in the Windows NT group, can then connect to the SQL Server installation using Windows NT Authentication. Each Windows NT account or group name is stored in master.dbo.sysxlogins.name. The Windows NT security_identifier for the Windows NT account or group is stored in syslogins.sid.

  • SQL Server login names

    These are used by users logging in using SQL Server Authentication. SQL Server login names are defined by members of the sysadmin or securityadmin fixed server roles using sp_addlogin. Each SQL Server login name is stored in master.dbo.sysxlogins.name. SQL Server generates a GUID that is used as a security_identifier and stores it in sysxlogins.sid.

  SQL Server version 7.0 uses master.dbo.sysxlogins.sid as the security_identifier for the login name. Earlier versions of SQL Server instead used a server_user_id from a different table: master.dbo.syslogins.suid. For backward compatibility, SQL Server 7.0 implements syslogins as a view of sysxlogins, and syslogins.suid is a computed column that computes a server_user_id from the security_identifier in sysxlogins.

• Database username

  Each Windows NT account or SQL Server login must be associated with a username in each database they are authorized to access. Database usernames are defined by members of the db_owner or db_accessadmin fixed database role, and are stored in the sysusers table found in each database. Each database username is associated with a database user ID that is stored in sysusers.uid.

  The security_identifier for each user is stored in sysusers.sid; therefore, users can be mapped back to their associated logins. It is less confusing if the members of the sysadmin, securityadmin, db_owner, and db_accessadmin roles use the same name for the database user as is used for the SQL Server login or Windows NT account; however, there is no requirement to do this.

For more information about login and database user accounts, see Logins, Users, Roles, and Groups.

Getting Login Accounts or IDs

When you are connected to SQL Server 7.0, use:

• SUSER_SNAME to get the SQL Server login name or Windows NT account associated with a security_identifier.
• SUSER_SID to get the security_identifier associated with a SQL Server login name or Windows NT account.
• SUSER_SID() (SUSER_SID specified without a login_account parameter) to get the security_identifier of the current connection regardless of whether SQL Server Authentication or Windows NT Authentication is used.
• The SQL-92 function SYSTEM_USER to get the Windows NT account for a Windows NT Authentication connection or the SQL Server login name for a SQL Server Authentication connection. In Transact-SQL, SYSTEM_USER is implemented as a synonym for SUSER_SNAME() (SUSER_SNAME specified without a security_identifier).

In SQL Server 7.0, the functions that return login names or accounts operate in this manner:

• SUSER_SNAME(security_identifier)

  SUSER_SNAME takes either:

  • The security_identifier for a Windows NT account or group, in which case it returns the name of the Windows NT account or group.
  • The pseudo security_identifier generated for a SQL Server login, in which case it returns the SQL Server login name.

  If a security_identifier is not specified for a connection made using Windows NT Authentication, SUSER_SNAME returns the name of the Windows NT account associated with the connection. If the connection was made using SQL Server Authentication, SUSER_SNAME returns the SQL Server login associated with the connection.

  If a system_user_id is specified instead of a security_identifier, SUSER_SNAME returns NULL.

• SUSER_NAME(system_user_id)

  This function is supported for backward compatibility. Use SUSER_SNAME when connected to SQL Server 7.0. SUSER_NAME takes either:

  • The system_user_id generated for a Windows NT account or group, in which case it returns the name of the Windows NT account or group.
  • The system_user_id generated for a SQL Server login, in which case it returns the SQL Server login name.

  If a system_user_id is not specified for a connection made using Windows NT Authentication, SUSER_NAME returns NULL. If the connection was made using SQL Server Authentication, SUSER_NAME returns the SQL Server login associated with the connection.

  If a security_identifier is specified instead of a system_user_id, SUSER_NAME returns NULL.

• SUSER_SID(‘login_account’)

  This function is supported for backward compatibility. Use SUSER_SID when connected to SQL Server 7.0. login_account can be either:

  • A Windows NT account or group name, in which case SUSER_SID returns the Windows NT security_identifier of the Windows NT account or group.
  • A SQL Server login name, in which case SUSER_SID returns the pseudo security_identifier generated for that SQL Server login name.

  If a login_account is not specified on a connection made using Windows NT Authentication, SUSER_SID returns the Windows NT security_identifier associated with the connection. If the connection was made using SQL Server Authentication, SUSER_SNAME returns the pseudo security_identifier associated with the connection.

• SUSER_ID(‘login_account’)

  This function is supported for backward compatibility. Use SUSER_SID when connected to SQL Server 7.0. login_account can be either of these:

  • A Windows NT account or group name, in which case SUSER_ID returns the Windows NT system_user_id of the Windows NT account or group.
  • A SQL Server login name, in which case SUSER_ID returns the pseudo system_user_id generated for that SQL Server login name.

  If a login_account is not specified on a connection made using Windows NT Authentication, SUSER_SID returns NULL. If the connection was made using SQL Server Authentication, SUSER_SNAME returns the system_user_id associated with the connection.

• SYSTEM_USER

  This SQL-92 function is implemented as a synonym for SUSER_SNAME() (SUSER_SNAME specified without a security_identifier parameter) in Transact-SQL.

Getting Database Usernames or User IDs

When you are connected to SQL Server 7.0, use:

• USER_ID to get the database user ID associated with a database user name.
• USER_ID() to get the database user ID associated with the current connection.
• USER_NAME to get the database username associated with a database user ID.
• Either the SQL-92 CURRENT_USER or SESSION_USER functions to get the database username associated with the current connection. In Transact-SQL these functions are implemented as synonyms for the USER_NAME() (USER_NAME specified without a database_user_ID parameter). The Transact-SQL function USER is also implemented as a synonym for USER_NAME().

  SQL-92 allows for SQL statements to be coded in SQL modules that can have authorization identifiers separate from the authorization identifier of the user that has connected to an SQL database. SQL-92 specifies that SESSION_USER always returns the authorization identifier of the user that made the connection. CURRENT_USER returns the authorization identifier of the SQL module for any statements executed from an SQL module, or of the user that made the connection if the SQL statements were not executed from an SQL module. If the SQL module does not have a separate authorization identifier, SQL-92 specifies that CURRENT_USER returns the same value as SESSION_USER. Microsoft SQL Server does not have separate authorization identifiers for SQL modules; therefore, CURRENT_USER and SESSION_USER are always the same. The USER function is defined by SQL-92 as a backward compatibility function for applications written to earlier versions of the standard. It is specified to return the same value as CURRENT_USER.

In SQL Server 7.0, the functions that return login names or accounts operate in this manner:

• USER_ID(‘database_user_name’)

  USER_ID returns the database user ID associated with the specified database username. If database_user_name is not specified USER_ID returns the database user ID associated with the current connection.

• USER_NAME(database_user_ID)

  USER_NAME returns the database user name associated with the specified database user ID. If database_user_ID is not specified USER_NAME returns the database username associated with the current connection.

• CURRENT_USER, SESSION_USER, USER

  These functions are synonyms for USER_NAME() (USER NAME specified without a database_user_ID parameter).

See Also