Retrieving the NT User Name from a query.

Question

Post reply

Retrieving the NT User Name from a query.

hmbtx

SSCarpal Tunnel

Points: 4664
More actions
November 17, 2005 at 10:19 am

#92297

I need to retrieve the NT User Name from all PC's logged into MS SQL Server 2000. The following is a portion of a query that I use. However, on some PC's the NT User Name is returned as blank or null. On the majority of the PC's I do get the NT User Name.
Select d.name,p.hostname,p.nt_username
from master.dbo.sysprocesses p (nolock)
join master..sysdatabases d (nolock) on p.dbid = d.dbid
Does anyone know what could cause the NT User Name not to be returned?
Thanks,
Howard

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

noeld SSC Guru Points: 96590 More actions · Answer 1

If that system is using SQL Authentication instead of Windows authentication you will get that

* Noel

hmbtx SSCarpal Tunnel Points: 4664 More actions · Answer 2

hmbtx

SSCarpal Tunnel

Points: 4664

November 17, 2005 at 1:23 pm

#604517

The authentication is the same on all PC's.

Mark Harr SSCrazy Eights Points: 9921 More actions · Answer 3

It has been my experience that nt_username will only have a value if the connection was made using Windows Authentication (Integrated Security=SSPI in the ADO connection string, Trusted_Connection=Yes in ODBC connection string).

If the user used a SQL login, this value would be blank (empty string). I have not seen that value be null in any instance.

Mark

hmbtx SSCarpal Tunnel Points: 4664 More actions · Answer 4

All users are logged into SQl with sql-authentication and for the majority of users the nt user name does appear.

Howard

Roy Rogers SSCertifiable Points: 7712 More actions · Answer 5

If the access is through an application using ADO via application user. Then connection string property can be used to specify the NT User as the parameter in connection string and value can be seen in the database. If it is SQL query Analyzer then NT Authentication is the only way.

Regards,
gova

hmbtx SSCarpal Tunnel Points: 4664 More actions · Answer 6

I am using a Rapid Application Tool (Magic from Magic Software Enteprises) to connect to the database. My login consists of Database Server name, User Name, User Password, (User name and password refer to SQL and not Windows user) and Connection String. Right now the connection string is blank.

Can you explain what you mean by "Then connection string property can be used to specify the NT User as the parameter in connection string and value can be seen in the database."

Thanks,

Howard

Roy Rogers SSCertifiable Points: 7712 More actions · Answer 7

Sorry I missed this post.

Workstation ID is the property of SQLConnection's connection string property.

http://msdn.microsoft.com/library/en-us/cpref/html/frlrfSystemDataSqlClientSqlConnectionClassConnectionStringTopic

If you pass the Workstation ID as your NTID then you can get the NTUser as

select host_name() in SQL server.

Example

SqlConnection myConnection = new SqlConnection();

myConnection.ConnectionString = "database=database;server=mySQLServer;User ID=ApplicationUser;Pwd=password;Connect Timeout=30;Workstation ID=YourNTUser";

myConnection.Open();

On this connection host_name() will be set as your NT User. This is an work around to use SQL authentication and get NTUSer in the SQL server.

Regards,
gova

hmbtx SSCarpal Tunnel Points: 4664 More actions · Answer 8

Govinn:

Thanks for the reply.

I am using a Rapid Application Tool (Magic from Magic Software Enteprises) to connect to the database. ALL logins consists of Database Server name, User Name, User Password, (User name and password refer to SQL and not Windows user) and Connection String. Right now the connection string is blank.

I use the following query:

Select d.name,p.hostname,p.nt_username

from master.dbo.sysprocesses p (nolock)

join master..sysdatabases d (nolock) on p.dbid = d.dbid

and d.name <> 'master' and d.name <> 'msdb'

Do you know why the majority of the connections show a NT User Name and only one or two do not using the above query when ALL USER LOGINS are the same method.

Is there any change to the above query to resolve this issue?

Howard

Roy Rogers SSCertifiable Points: 7712 More actions · Answer 9

It would be possible to use NTID as WorkStationID when you use ADO to connect to the databse. I don't know about the others.

If the connection string is blank SQL Server will have loging machine name as host_name().

Changing the query is not the solution as with not NT Authentication or WorkStation ID is not passed explicitly SQL Server does not know the NT user.

Sorry as this is not a solution you are looking for.

Regards,
gova