How to use getdate() and user_name() functions with Windows NT login

I am connecting to my SQL Server 2000 database with a Windows NT security
login. Several of my tables require a timestamp and user id for row added.
I use getdate() for the former default and user_name() for the latter. This
works great if I'm using any SQL logins-- however, neither of the functions
returns anything if I am logged in as an NT user.
Any ideas why not, and how to fix?
Thanks,
Randall ArnoldHi, try suser_sname() instead
select suser_sname()
Denis the SQL Menace
http://sqlservercode.blogspot.com/
select suser_sname()
Randall Arnold wrote:
> I am connecting to my SQL Server 2000 database with a Windows NT security
> login. Several of my tables require a timestamp and user id for row added
.
> I use getdate() for the former default and user_name() for the latter. Th
is
> works great if I'm using any SQL logins-- however, neither of the function
s
> returns anything if I am logged in as an NT user.
> Any ideas why not, and how to fix?
> Thanks,
> Randall Arnold|||Thanks, but I'm not using either function in a select statement; I'm placing
them in the Default Value property of the appropriate column. Besides,
getdate() is also not returning anything when using an NT login. This makes
no sense to me.
Randall Arnold
"SQL Menace" <denis.gobo@.gmail.com> wrote in message
news:1149860914.178963.188360@.f6g2000cwb.googlegroups.com...
> Hi, try suser_sname() instead
> select suser_sname()
>
> Denis the SQL Menace
> http://sqlservercode.blogspot.com/
>
> select suser_sname()
> Randall Arnold wrote:
>|||Can you post DDL?
You know defaults only get populated on inserts not on updates right?
Denis the SQL Menace
http://sqlservercode.blogspot.com/
Randall Arnold wrote:
> Thanks, but I'm not using either function in a select statement; I'm placi
ng
> them in the Default Value property of the appropriate column. Besides,
> getdate() is also not returning anything when using an NT login. This mak
es
> no sense to me.
> Randall Arnold
> "SQL Menace" <denis.gobo@.gmail.com> wrote in message
> news:1149860914.178963.188360@.f6g2000cwb.googlegroups.com...|||Yes, I know the defaults only get populated on Inserts.
As I noted, both functions work 100% as expected when logging in as a SQL
user-- the problem only occurs if I use a Windows NT security login account.
The results are the same for every table using these functions to populate
the default values.
Here's one of the tables in question:
CREATE TABLE [dbo].[L_QUALITY_AUDIT_DETAIL] (
[Detail_ID] [int] NOT NULL ,
[Audit_ID] [int] NOT NULL ,
[User_ID] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Start_TS] [datetime] NOT NULL ,
[Complete_TS] [datetime] NULL ,
[ID] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Scales_Result] [real] NULL ,
[pid_fAIL_fLAG] [bit] NOT NULL ,
[Fail_Flag] [bit] NOT NULL ,
[Stamp] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Create_TS] [datetime] NOT NULL ,
[Create_UID] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Modify_TS] [datetime] NULL ,
[Modify_UID] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
Create_TS uses getdate() to populate default value; Create_UID uses
user_name().
Thanks,
Randall Arnold
"SQL Menace" <denis.gobo@.gmail.com> wrote in message
news:1149861709.031952.256660@.g10g2000cwb.googlegroups.com...
> Can you post DDL?
> You know defaults only get populated on inserts not on updates right?
> Denis the SQL Menace
> http://sqlservercode.blogspot.com/
> Randall Arnold wrote:
>|||Never mind-- for some unknown reason it's working now. Go figure!
Randall Arnold
"SQL Menace" <denis.gobo@.gmail.com> wrote in message
news:1149861709.031952.256660@.g10g2000cwb.googlegroups.com...
> Can you post DDL?
> You know defaults only get populated on inserts not on updates right?
> Denis the SQL Menace
> http://sqlservercode.blogspot.com/
> Randall Arnold wrote:
>|||Perhaps sunspots? ;-)
Denis the SQL Menace
http://sqlservercode.blogspot.com/
Randall Arnold wrote:
> Never mind-- for some unknown reason it's working now. Go figure!
> Randall Arnold
> "SQL Menace" <denis.gobo@.gmail.com> wrote in message
> news:1149861709.031952.256660@.g10g2000cwb.googlegroups.com...|||I can't reproduce, I created the table using Windows Auth, inserted a row
using Windows auth, and inserted a row using sql auth, and both rows show up
populated correctly. I repeated the process by creating the table with SQL
auth, and inserted a row using both, with the same results. Here is my
simplified schema (including the formula for the default values, which you
left out!).
CREATE TABLE [dbo].[L_QUALITY_AUDIT_DETAIL]
(
[Detail_ID] [int] NOT NULL ,
[Create_TS] [datetime] NOT NULL
DEFAULT GETDATE(),
[Create_UID] [varchar] (15) NOT NULL
DEFAULT SUSER_SNAME()
)
GO
INSERT L_Quality_Audit_Detail(Detail_ID) SELECT 1;
GO
Now, in a window using the other security credentials:
INSERT L_Quality_Audit_Detail(Detail_ID) SELECT 2;
GO
SELECT * FROM L_Quality_Audit_Detail;
GO
Can you reproduce your problem with the above code? If so, then that is
peculiar. If not, then either there is a trigger attached to the table
which is messing things up, or there is a problem with the default formulae
or the table itself.
A
"Randall Arnold" <randall.nospam.arnold@.nospamnokia.com.> wrote in message
news:EIfig.31647$Nb2.577506@.news1.nokia.com...
> Yes, I know the defaults only get populated on Inserts.
> As I noted, both functions work 100% as expected when logging in as a SQL
> user-- the problem only occurs if I use a Windows NT security login
> account. The results are the same for every table using these functions to
> populate the default values.
> Here's one of the tables in question:
> CREATE TABLE [dbo].[L_QUALITY_AUDIT_DETAIL] (
> [Detail_ID] [int] NOT NULL ,
> [Audit_ID] [int] NOT NULL ,
> [User_ID] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [Start_TS] [datetime] NOT NULL ,
> [Complete_TS] [datetime] NULL ,
> [ID] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [Scales_Result] [real] NULL ,
> [pid_fAIL_fLAG] [bit] NOT NULL ,
> [Fail_Flag] [bit] NOT NULL ,
> [Stamp] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Create_TS] [datetime] NOT NULL ,
> [Create_UID] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
> ,
> [Modify_TS] [datetime] NULL ,
> [Modify_UID] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> Create_TS uses getdate() to populate default value; Create_UID uses
> user_name().
> Thanks,
> Randall Arnold
>
> "SQL Menace" <denis.gobo@.gmail.com> wrote in message
> news:1149861709.031952.256660@.g10g2000cwb.googlegroups.com...
>|||I don't get it myself-- it wasn't working earlier, and just suddenly decided
to play nice. First time I've seen this and hope it's the last...
Randall Arnold
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:OlWuVV9iGHA.4660@.TK2MSFTNGP03.phx.gbl...
>I can't reproduce, I created the table using Windows Auth, inserted a row
>using Windows auth, and inserted a row using sql auth, and both rows show
>up populated correctly. I repeated the process by creating the table with
>SQL auth, and inserted a row using both, with the same results. Here is my
>simplified schema (including the formula for the default values, which you
>left out!).
> CREATE TABLE [dbo].[L_QUALITY_AUDIT_DETAIL]
> (
> [Detail_ID] [int] NOT NULL ,
> [Create_TS] [datetime] NOT NULL
> DEFAULT GETDATE(),
> [Create_UID] [varchar] (15) NOT NULL
> DEFAULT SUSER_SNAME()
> )
> GO
> INSERT L_Quality_Audit_Detail(Detail_ID) SELECT 1;
> GO
> Now, in a window using the other security credentials:
> INSERT L_Quality_Audit_Detail(Detail_ID) SELECT 2;
> GO
> SELECT * FROM L_Quality_Audit_Detail;
> GO
> Can you reproduce your problem with the above code? If so, then that is
> peculiar. If not, then either there is a trigger attached to the table
> which is messing things up, or there is a problem with the default
> formulae or the table itself.
> A
>
>
> "Randall Arnold" <randall.nospam.arnold@.nospamnokia.com.> wrote in message
> news:EIfig.31647$Nb2.577506@.news1.nokia.com...
>