Export (0) Print
Expand All
This topic has not yet been rated - Rate this topic

Functions That Return User Names and User IDs

Several system functions return user names and user IDs. Understanding the parameters and output of these functions requires an understanding of the types of names and IDs that are used in SQL Server.

Each user that logs on to SQL Server has the following two levels of names in SQL Server, and each name is associated with a unique ID:

  • Login name

    Each user authorized to log on to SQL Server has one login name that gives the user access to an instance of SQL Server. There are two types of login names:

    • Microsoft Windows account names

      By using sp_grantlogin, members of the sysadmin or securityadmin fixed server roles can authorize the Windows accounts of individual users or Windows groups to log on to an instance of SQL Server. The user identified by the Windows account, or any person in the Windows group, can then connect to an instance of SQL Server by using Windows Authentication. Each Windows account or group name is stored in sys.server_principals. The Windows security identifier for the Windows account or group is stored in sys.server_principals.sid.

    • SQL Server login names

      These are used when user log on by using SQL Server Authentication. SQL Server login names are defined by members of the sysadmin or securityadmin fixed server roles by using sp_addlogin. Each SQL Server login name is stored in master.dbo.syslogins.loginname. SQL Server generates a GUID that is used as a security identifier and stores it in sys.server_principals.sid.

      SQL Server uses sys.server_principals.sid as the security_identifier for the login name.

  • Database user name

    Each Windows account or SQL Server login must be associated with a user name in each database that the user is authorized to access, or the database must have guest access enabled. Database user names are defined by members of the db_owner or db_accessadmin fixed database role, and are stored in the sys.database_principals table found in each database. Each database user name is associated with a database user ID stored in sys.database_principals.uid.

    The security identifier for each user is stored in sys.database_principals.sid; therefore, users can be mapped back to their associated logins. It is less confusing if the same name for the database user is used for the SQL Server login or Windows account; however, there is no requirement to do this.

When you are connected to an instance of SQL Server, you can use the following to obtain login accounts or IDs:

  • SUSER_SNAME to obtain the SQL Server login name or Windows account associated with a security identifier.

  • SUSER_SID to obtain the security identifier associated with a SQL Server login name or Windows account.

  • SUSER_SID() (SUSER_SID specified without a login_account parameter.) to obtain the security identifier of the current connection, regardless of whether SQL Server Authentication or Windows Authentication is used.

  • The ISO function SYSTEM_USER to obtain the Windows account for a Windows Authentication connection or the SQL Server login name for an SQL Server Authentication connection. In Transact-SQL, SYSTEM_USER is implemented as a synonym for SUSER_SNAME() (SUSER_SNAME specified without a security_identifier parameter.).

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

  • SUSER_SNAME(security_identifier)

    SUSER_SNAME takes either:

    • The security_identifier for a Windows account or group, in which case it returns the name of the Windows 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 that is made by using Windows Authentication, SUSER_SNAME returns the name of the Windows account that is associated with the connection. If the connection was made using SQL Server Authentication, SUSER_SNAME returns the SQL Server login associated with the connection.

  • SYSTEM_USER

    In ISO function is implemented as a synonym for SUSER_SNAME(). (SUSER_SNAME specified without a security_identifier parameter.)

When you are connected to an instance of SQL Server, use the following to obtain user name or user IDs:

  • USER_ID to obtain the database user ID associated with a database user name.

  • USER_ID() to obtain the database user ID associated with the current connection.

  • USER_NAME to obtain the database user name associated with a database user ID.

  • Either the ISO CURRENT_USER or SESSION_USER functions to obtain the database user name associated with the current connection. In Transact-SQL, these functions are implemented as synonyms for 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().

    ISO 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. ISO specifies that SESSION_USER always return 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, ISO specifies that CURRENT_USER return the same value as SESSION_USER. 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 ISO 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, the functions that return login names or accounts operate in the following manner:

  • USER_ID('database_user_name')

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

  • USER_NAME(database_user_ID)

    USER_NAME returns the database user name that is associated with the specified database user ID. If database_user_ID is not specified, USER_NAME returns the database user name that is 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.)

Did you find this helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2014 Microsoft. All rights reserved.