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 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:
- Login names.
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:
- Microsoft Windows® account 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.
- SQL Server login names.
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.
- Microsoft Windows® account names.
- Database user 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.
Getting Login Accounts or IDs
When connected to SQL Server 2000, use:
- SUSER_SNAME to get the SQL Server login name or Windows 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 Authentication is used.
- The SQL-92 function SYSTEM_USER to get 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).
In SQL Server 2000, 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 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 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.
- The security_identifier for a Windows account or group, in which case it returns the name of the Windows account or group.
- 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 User Names or User IDs
When connected to SQL Server 2000, 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 user name associated with a database user ID.
- Either the SQL-92 CURRENT_USER or SESSION_USER functions to get the database user name 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 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('database_user_name')
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(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 user name 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).