SQL Session

SQL Session

A SQL session is an occurrence of a user interacting with a relational database through the use of SQL commands. When a user initially connects to the database, a session is established.

Within the scope of a SQL session, valid SQL commands can be entered
to query the database, manipulate data in the database, and define database structures, such as tables.

A session may be invoked by either direct connection to the database or through a front-end application. In both cases, sessions are normally established by a user at a terminal or workstation that communicates through a network with the computer that hosts the database. [Via]

Each SQL session is associated with a user identifier and role name.

Every SQL session is started by a user. That user's user identifier is called the SQL-session user identifier. The privileges associated with the SQL-session user identifier determine what privileges that user has and what actions he or she may perform during the session.

When your SQL session starts, your SQL-session user identifier is also the current user identifier. The identity of the current user is kept in a special value named CURRENT_USER, which can be queried to find out who is currently in charge of a session.

Just as a session initiated by a user is associated with an SQL-session user identifier, it is also associated with an SQL-session role name.

The value of the current role name is available in the CURRENT_ROLE special value. When an SQL session is created, the current role name has a null value. At any given instant, either a user identifier is specified and the associated role name has a null value, or a role name is specified and the associated user identifier has a null value. A SET ROLE statement can create a situation in which both the user identifier for a session and a role name are non-null. In such a case, the privileges assigned to both the user identifier and to the role name are available to the user. [Source: SQL All-in-One For Dummies By Allen G. Taylor]

To get the session ID, type:

SELECT @@SPID