Problem 0f @@SPID


I understand that this @@SPID will return the server process ID of the current user process. So, I use this spid to select the username from the NT in ?master..sysprocesses?  at column ?nt_username?. The below sp is my code;

CREATE PROCEDURE proc_ NTUserName
AS
BEGIN
DECLARE @vchEmployee varchar(12)
     DECLARE @NTName nchar(128)
     DECLARE @SlashPosition INT

     SELECT @NTName = nt_username -- Fetch NT user name for this process ID
     FROM master..sysprocesses
     WHERE spid = @@SPID

SET @SlashPosition = CHARINDEX('\', @NTName) -- Trim the NT domain from front of user name if it is present

     IF (@SlashPosition <> 0)
     BEGIN
          SET @NTName = RIGHT(@NTName, 128 - @SlashPosition)
     END

SET @vchEmployee = RTRIM(LEFT(CAST(@NTName AS varchar(256)), 12)) -- Convert to data type used in audit table
     
     SELECT vchEmployee
END


When I test this sp in SQL Server Query Analyzer it is works fine but when I use recordset in Access to retrieve the username it is return nothing (empty). And my code in access is as below

Dim strsql As String
Dim rs As Recordset

    strsql = " proc_NTUserName  "
    Set rs = clsDatabase.OpenRecordSetODBC(strsql)
    If Not rs.EOF Then
        MsgBox rs(0)
    End If
    Set rs = Nothing

So, why from access it cannot return value????

     
jetyunAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
<jetyun> or <Kelvsat>

as i mentionned: If the user has used the Integrated Security ...
If you use UserID= and Password= parameters in your connection string, the value(s) nt_user and nt_domain ARE empty!
Your connection string needs to have "Trusted_Connection=true" or "Integrated Security=SSPI" in it in order that these values are filled.

Please post your connection string so we can compare.

CHeers
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
If the user has used the Integrated Security, then simply issue this:

select system_user
or
select nt_domain, nt_user from sysprocesses where spid = @@spid

Now in your precise case, you might modify your code like this:
CREATE PROCEDURE proc_ NTUserName
AS
SET NOCOUNT ON
BEGIN
 ...
END

The problem is your first SELECT @Variable, which will generate an empty recordset, which will not be generated with the SET NOCOUNT ON option.

Cheers
0
 
KelvsatCommented:
dear angelIII,
  I tried this already it is still not work....
0
 
jetyunAuthor Commented:
i stop doing that already......thanx for ur comment.
0
All Courses

From novice to tech pro — start learning today.