SESSION_USER

Is a niladic function that allows a system-supplied value for the current session's username to be inserted into a table when no default value is specified. Also allows the username to be used in queries, error messages, and so on.

Syntax

SESSION_USER

Return Types

nchar

Remarks

Use SESSION_USER with DEFAULT constraints in either the CREATE TABLE or ALTER TABLE statements, or use as any standard function.

Examples

A. Use SESSION_USER to return the session's current username

This example declares a variable as char, assigns the current value of SESSION_USER, and then prints the variable with a text description.

DECLARE @session_usr char(30)
SET @session_usr = SESSION_USER
SELECT 'This session''s current user is: '+ @session_usr
GO

Here is the result set:

-------------------------------------------------------------- 
This session's current user is: dbo                            

(1 row(s) affected)

B. Use SESSION_USER with DEFAULT constraints

This example creates a table using the SESSION_USER niladic function as a DEFAULT constraint for the delivery person.

USE pubs
GO
CREATE TABLE deliveries2
(
 order_id int IDENTITY(5000, 1) NOT NULL,
 cust_id  int NOT NULL,
 order_date datetime NOT NULL DEFAULT GETDATE(),
 delivery_date datetime NOT NULL DEFAULT DATEADD(dd, 10, GETDATE()),
 delivery_person char(30) NOT NULL DEFAULT SESSION_USER
)
GO
INSERT deliveries2 (cust_id)
VALUES (7510)
INSERT deliveries2 (cust_id)
VALUES (7231)
INSERT deliveries2 (cust_id)
VALUES (7028)
INSERT deliveries2 (cust_id)
VALUES (7392)
INSERT deliveries2 (cust_id)
VALUES (7452)
GO

This query selects all information from the deliveries2 table.

SELECT order_id AS 'Ord#', cust_id AS 'Cust#', order_date, 
   delivery_date, delivery_person AS 'Delivery'
FROM deliveries2
ORDER BY order_id
GO

Here is the result set:

Ord#  Cust#  order_date          delivery_date        Delivery     
----  ------ ------------------  -------------------- ----------------
5000  7510   Mar 4 1998 10:21AM  Mar 14 1998 10:21AM  dbo                            
5001  7231   Mar 4 1998 10:21AM  Mar 14 1998 10:21AM  dbo                            
5002  7028   Mar 4 1998 10:21AM  Mar 14 1998 10:21AM  dbo                            
5003  7392   Mar 4 1998 10:21AM  Mar 14 1998 10:21AM  dbo                            
5004  7452   Mar 4 1998 10:21AM  Mar 14 1998 10:21AM  dbo                            

(5 row(s) affected)

See Also

ALTER TABLE

CREATE TABLE

CURRENT_TIMESTAMP

CURRENT_USER

SYSTEM_USER

System Functions

USER

USER_NAME