Unable to use DEFAULT SESSION_USER in table constraint (switches to user_name instead)

Question

I have defined a constraint with a default value of SESSION_USER, eg as follows:

CREATE TABLE [test] (col1 varchar(50))
GO
ALTER TABLE [test]  ADD  CONSTRAINT [x]  DEFAULT SESSION_USER FOR [col1]
GO

This seems to run fine - Command(s) completed successfully.

However - the actual definition has been modified to use the user_name() function, eg as follows:

ALTER TABLE [dbo].[test] ADD  CONSTRAINT [x]  DEFAULT (user_name()) FOR [col1]

Why is this?

Answer 1

Because SESSION_USER is a synonym for USER_NAME():

When you are connected to an instance of SQL Server, use the following to obtain user name or user IDs:

...

• Either the ISO CURRENT_USER or SESSION_USER functions to obtain the database user name associated with the current connection. In Transact-SQL, these functions are implemented as synonyms for USER_NAME(). (USER_NAME specified without a database_user_ID parameter.) The Transact-SQL function USER is also implemented as a synonym for USER_NAME().

(Source: MSDN).

Constraints are reduced to a normal form on compilation rather than being stored as-is like stored procedures are. You'll notice that extra parenthesis have been added as well. While the literal definition is not retained, the semantics are.