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

USER

SQL Server 2000

Allows a system-supplied value for the current user's database username to be inserted into a table when no default value is specified.

Syntax

USER

Return Types

char

Remarks

USER provides the same functionality as the USER_NAME system function.

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

Examples
A. Use USER to return the current user's database username

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

DECLARE @usr char(30)
SET @usr = user
SELECT 'The current user''s database username is: '+ @usr
GO

Here is the result set:

----------------------------------------------------------------------- 
The current user's database username is: dbo                            

(1 row(s) affected)
B. Use USER with DEFAULT constraints

This example creates a table using USER as a DEFAULT constraint for the salesperson of a sales row.

USE pubs
GO
CREATE TABLE inventory2
(
 part_id int IDENTITY(100, 1) NOT NULL,
 description varchar(30) NOT NULL,
 entry_person varchar(30) NOT NULL DEFAULT USER 
)
GO
INSERT inventory2 (description)
VALUES ('Red pencil')
INSERT inventory2 (description)
VALUES ('Blue pencil')
INSERT inventory2 (description)
VALUES ('Green pencil')
INSERT inventory2 (description)
VALUES ('Black pencil')
INSERT inventory2 (description)
VALUES ('Yellow pencil')
GO

This is the query to select all information from the inventory2 table:

SELECT * 
FROM inventory2
ORDER BY part_id
GO

Here is the result set (note the entry-person value):

part_id     description                    entry_person                   
----------- ------------------------------ -----------------------------
100         Red pencil                     dbo                            
101         Blue pencil                    dbo                            
102         Green pencil                   dbo                            
103         Black pencil                   dbo                            
104         Yellow pencil                  dbo                            

(5 row(s) affected)

See Also

ALTER TABLE

CREATE TABLE

Creating and Modifying PRIMARY KEY Constraints

CURRENT_TIMESTAMP

CURRENT_USER

Modifying Column Properties

Security Functions

SESSION_USER

SYSTEM_USER

USER_NAME

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