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 used in Microsoft® SQL Server™ 2000.
Each user logging on to SQL Server has two levels of names in SQL Server, and each name is associated with a unique ID:
Each user authorized to log on to SQL Server has one login name that gives them access to an instance of SQL Server. There are two types of login names:
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 using sp_grantlogin. The user identified by the Windows account, or any person in the Windows group, can then connect to an instance of SQL Server using Windows Authentication. Each Windows account or group name is stored in master.dbo.syslogins.loginname. The Windows security_identifier for the Windows account or group is stored in syslogins.sid.
These are used when logging-on 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.syslogins.loginname. SQL Server generates a GUID used as a security_identifier and stores it in syslogins.sid.
SQL Server 2000 uses master.dbo.syslogins.sid as the security_identifier for the login name.
Each Windows account or SQL Server login must be associated with a user name in each database 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 sysusers table found in each database. Each database user name is associated with a database user ID 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 same name for the database user is used for the SQL Server login or Windows account; however, there is no requirement to do this.
For more information about login and database user accounts, see Logins, Users, Roles, and Groups.
When connected to SQL Server 2000, use:
In SQL Server 2000, the functions that return login names or accounts operate in this manner:
SUSER_SNAME takes either:
If a security_identifier is not specified for a connection made using Windows Authentication, SUSER_SNAME returns the name of the Windows 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.
This SQL-92 function is implemented as a synonym for SUSER_SNAME() (SUSER_SNAME specified without a security_identifier parameter) in Transact-SQL.
When connected to SQL Server 2000, use:
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 a SQL database. SQL-92 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 a SQL module, or of the user that made the connection if the SQL statements were not executed from a SQL module. If the SQL module does not have a separate authorization identifier, SQL-92 specifies that CURRENT_USER return 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, the functions that return login names or accounts operate in this manner:
USER_ID returns the database user ID associated with the specified database user name. If database_user_name is not specified, USER_ID returns the database user ID associated with the current connection.
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 user name associated with the current connection.
These functions are synonyms for USER_NAME() (USER NAME specified without a database_user_ID parameter).