Export (0) Print
Expand All
ABS
ALL
AND
ANY
AVG
bit
COS
COT
DAY
EXP
GO
IN
LEN
LOG
MAX
MIN
NOT
OR
PI
SET
SIN
STR
SUM
TAN
USE
VAR
Expand Minimize

SESSION_USER

SQL Server 2000

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

Was this page helpful?
(1500 characters remaining)
Thank you for your feedback
Show:
© 2014 Microsoft