USER (Transact-SQL)
Applies to:
SQL Server
Azure SQL Database
Azure SQL Managed Instance
Azure Synapse Analytics
Analytics Platform System (PDW)
Allows a system-supplied value for the database user name of the current user to be inserted into a table when no default value is specified.
Transact-SQL syntax conventions
Syntax
USER
Note
To view Transact-SQL syntax for SQL Server 2014 (12.x) and earlier versions, see Previous versions documentation.
Return Types
nvarchar(128)
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.
USER always returns the name of the current context. When called after an EXECUTE AS statement, USER returns the name of the impersonated context.
If a Windows principal accesses the database by way of membership in a group, USER returns the name of the Windows principal instead of the name of the group.
Examples
A. Using USER to return the database user name
The following 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. Using USER with DEFAULT constraints
The following example creates a table by using USER
as a DEFAULT
constraint for the salesperson of a sales row.
USE AdventureWorks2022;
GO
CREATE TABLE inventory22
(
part_id INT IDENTITY(100, 1) NOT NULL,
description VARCHAR(30) NOT NULL,
entry_person VARCHAR(30) NOT NULL DEFAULT USER
)
GO
INSERT inventory22 (description)
VALUES ('Red pencil')
INSERT inventory22 (description)
VALUES ('Blue pencil')
INSERT inventory22 (description)
VALUES ('Green pencil')
INSERT inventory22 (description)
VALUES ('Black pencil')
INSERT inventory22 (description)
VALUES ('Yellow pencil')
GO
This is the query to select all information from the inventory22
table:
SELECT * FROM inventory22 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)
C. Using USER in combination with EXECUTE AS
The following example illustrates the behavior of USER
when called inside an impersonated session.
SELECT USER;
GO
EXECUTE AS USER = 'Mario';
GO
SELECT USER;
GO
REVERT;
GO
SELECT USER;
GO
Here is the result set.
DBO
Mario
DBO
See Also
ALTER TABLE (Transact-SQL)
CREATE TABLE (Transact-SQL)
CURRENT_TIMESTAMP (Transact-SQL)
CURRENT_USER (Transact-SQL)
Security Functions (Transact-SQL)
SESSION_USER (Transact-SQL)
SYSTEM_USER (Transact-SQL)
USER_NAME (Transact-SQL)
คำติชม
https://aka.ms/ContentUserFeedback
เร็วๆ นี้: ตลอดปี 2024 เราจะขจัดปัญหา GitHub เพื่อเป็นกลไกคำติชมสำหรับเนื้อหา และแทนที่ด้วยระบบคำติชมใหม่ สำหรับข้อมูลเพิ่มเติม ให้ดู:ส่งและดูข้อคิดเห็นสำหรับ