Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2004

    Question Unanswered: ODBC Trusted_Connection call to SQLGetInfo returns DBO as user name, not NT User

    Hello, all:
    I am connecting to SQL Server 2000 with a trusted connection, and that is working fine. I then am issuing a SQLGetInfo call to find out the SQL_USER_NAME, which is always returning dbo instead of (my) Windows NT login name, which I expect to see. THe authentication is (apparently) confirmed by the SQL Server session monitor where it shows the right Windows NT user name logged in, over ODBC. With a standard SQL Login, untrusted, I definitely get the right user name back, not dbo.


    Login Type / user id /returned value from SQLGetInfo

    Windows Authentication / Windows login / dbo
    SQL Authentication / Windows login (UID) / UID

    The problem is that I am trying to confirm that the userid entered in a dialog or passed on the command line to my application matches the actual connected user name inside SQL Server....

    My questions:
    Is there some pathology in Trusted_Connections that masks the Windows/NT login name and always returns UID 'dbo' from a call to SQLGetInfo? Is there a MSS (public) stored procedure that can robustly give back the logged in user name so I can bypass the SQLGetInfo call?

  2. #2
    Join Date
    Mar 2004

    Exclamation Solution, use SYSTEM_USER call to get Win NT login and parse it

    I think I have finally wrestled this beast to the ground. Here's the story, in case anybody is interested:

    1) When making an NTLM trusted connection to MSS, logging in using my NT login name e.g. as amarshall, the system was always saying the connected user is dbo, no matter what. This can be verified by using a SQL tool like SQL Query Analyzer, connecting over a trusted connection to MSS, and entering SELECT USER; or SELECT USER_NAME() or any variants. These will always return dbo, which is not the real 'user' of interest, but the schema owner. What we want is to confirm 'amarshall' is a (legitimate) user, meaning, SQL Server knows who this is..

    2) So, use the SYSTEM_USER call, and the result is AMARSHALL-CAM\amarshall (Host\user).

    3) Then, just perform substring match to see if what the user has typed into the login dialog (amarshall) matches what is in the call result. If so, good user, otherwise, reject (program decides).

    4) I do NOT strip off the host name or anything - just substring match result of SYSTEM_USER call to logins userid.

    5) Database connectstring for Powerbuilder must include SECURE=1. For ADO/VB/.NET, has to have something like TRUSTED_CONNECTION=yes (PB does this for me)

    6) Have not tested this against the SQL Server Desktop Edition. Should be identical, but who knows?

    7) If anybody has a superior resolution, or security concerns, please advise, but this suffices for now.

  3. #3
    Join Date
    Feb 2004
    In front of the computer
    Provided Answers: 54
    dbo is a database user. You seem to want the server login information, which is quite different. I'd use suser_sname.


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts