> 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 Arnold

"SQL Menace" <denis.g***@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:
>> 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 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.g***@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:
> >> 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 Arnold
> >

"SQL Menace" <denis.g***@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:
>> 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.g***@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:
>> >> 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 Arnold
>> >
>

"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.g***@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:
>>> 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.g***@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:
>>> >> 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 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...
>> 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.g***@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:
>>>> 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.g***@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:
>>>> >> 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 Arnold
>>>> >
>>>
>>
>>
>
>

"SQL Menace" <denis.g***@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:
>> 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.g***@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:
>> >> 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 Arnold
>> >
>

> Never mind-- for some unknown reason it's working now.  Go figure!
>
> Randall Arnold
>
> "SQL Menace" <denis.g***@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:
> >> 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.g***@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:
> >> >> 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 Arnold
> >> >
> >