September 26, 2007 at 8:39 am
Is it possible to find the individual logon of someone who has being granted access via a windows group logon. I seem to recall somewhere coming across a script that returns just that but cant for the life of me remember where it was (maybe I just dreamt it)
thanks for your help
Liam
September 27, 2007 at 4:06 am
Liam,
select nt_username from master..sysprocesses
will give you their individual id even if access id granted through group. alco column ny_domain if you need that.
HTH
george
---------------------------------------------------------------------
September 28, 2007 at 6:59 am
Curiosity got the better of me ...
-- Current user login information.
-- SQL Server 2000.
select
spid
,nt_domain
,loginame
,nt_username
,hostname
,login_time
from master..sysprocesses
-- Current user login information.
-- SQL Server 2005.
select
spid
,nt_domain
,loginame
,nt_username
,hostname
,login_time
from master.sys.sysprocesses
Regards,
"Key"
MCITP: DBA, MCSE, MCTS: SQL 2005, OCP
October 1, 2007 at 8:46 am
works a treat
thanks
October 2, 2007 at 7:26 am
I have tried both of the above scripts and the majority of the result lines display the nt_username but in some instances where the nt_username should be displayed it is not displayed.
Can anyone tell me why would this would be?
Thanks
Howard
October 2, 2007 at 8:12 am
Not sure. Are you sure it should be displayed? There could be system processes or SQL logins.
October 2, 2007 at 11:38 am
hmbtx,
In my environment, we have vendor applications that authenticate with a Windows/Active Directory user, and then the application "spawns" a generic user for SQL activity behind the scenes. Perhaps you are running into that scenario?
I have also updated the queries I posted above with a little more verbose information about each field as a comment.
-- Current user login information.
-- SQL Server 2000.
-- Column comments from SQL Server 2000 Books Online.
-- Topic: sysprocesses (Transact-SQL)
select
spid -- SQL Server process ID.
,dbid -- ID of the database currently being used by the process.
,uid -- ID of the user who executed the command.
,nt_domain -- Windows NT 4.0 domain for the client (if using Windows Authentication) or a trusted connection.
,loginame -- Login name.
,nt_username -- Windows NT 4.0 user name for the process (if using Windows Authentication) or a trusted connection.
,hostname -- Name of the workstation.
,program_name -- Name of the application program.
,login_time -- Time at which a client process logged into the server. For system processes, the time at which SQL Server startup occurred is stored.
from master..sysprocesses
-- Current user login information.
-- SQL Server 2005.
-- Column comments from SQL Server 2005 Books Online.
-- Topic: sys.sysprocesses (Transact-SQL)
select
spid -- SQL Server session ID.
,dbid -- ID of the database currently being used by the process.
,uid -- ID of the user that executed the command. Overflows or returns NULL if the number of users and roles exceeds 32,767. For more information, see Querying the SQL Server System Catalog.
,nt_domain -- Microsoft Windows domain for the client, if using Windows Authentication, or a trusted connection.
,loginame -- Login name.
,nt_username -- Windows user name for the process, if using Windows Authentication, or a trusted connection.
,hostname -- Name of the workstation.
,program_name -- Name of the application program.
,login_time -- Time at which a client process logged into the server. For system processes, the time at which the SQL Server startup occurred is stored.
from master.sys.sysprocesses
"Key"
MCITP: DBA, MCSE, MCTS: SQL 2005, OCP
October 2, 2007 at 4:29 pm
Damon:
Thanks for help. I tried your enhanced script but still no luck.
I am running MSSQL 2000sp4 on Windows NT4.0sp6a.
Also, the missing nt_username only ocurrs from the two new PC's just installed.
I will be more than happy to try any other suggestions that you might have.
Howard
October 3, 2007 at 6:57 am
Howard,
I will send you a Private Message with my contact information so that we can continue to troubleshoot your issue.
"Key"
MCITP: DBA, MCSE, MCTS: SQL 2005, OCP
October 3, 2007 at 3:50 pm
I have determined that the missing nt_username has to do with the PC workstation and not the user.
Background on the problem.
All users run the same program which authenticates with the same MSSQL 2000 user and not the Windows user.
I can retrieve the nt_username on all workstations with the exception of two new workstations that where just installed.
I had the user on one of the new workstations log onto another PC and run the application and the script returned her nt_username. When she went back to her new PC and ran the same application the nt_username was not returned.
I hope that this makes sense.
Does anyone know of what settings on a PC would cause the nt_username not to be returned? The PC is running Windows XP.
I am running MSSQL 2000 sp4 on Windows NT 4.0sp6a.
Thanks,
Howard
October 3, 2007 at 6:22 pm
hmbtx (10/3/2007)
Does anyone know of what settings on a PC would cause the nt_username not to be returned? The PC is running Windows XP.
This isn't determined by the PC configuration. The nt_username is populated if the connection made to SQL Server was via a Windows account. If it's not populated it's one of two things: a SQL Server login or a guest (if that's enabled) login (meaning it couldn't authenticate the user).
For either workstation that doesn't return the username, when the user connects, what is populated in the loginame field?
K. Brian Kelley
@kbriankelley
October 3, 2007 at 7:06 pm
You asked, "what is populated in the loginame field?"
The loginame contains the SQL Server login name. Every row returned from the query shows the same SQL Server login name (In Enterprise Manager it is >Security > Logins). Even the rows that do not return the nt_username return the loginame.
Remember, all users run the same program. That program connects to SQL Server with the same SQL Server login name.
I hope that I have answered your question. I need all the help that I can get on this problem. I have posted on various SQL lists and no one can come up with the answer.
Thanks,
Howard
October 3, 2007 at 8:41 pm
hmbtx (10/3/2007)
Remember, all users run the same program. That program connects to SQL Server with the same SQL Server login name.
If the program connects with a SQL Server login, not a Windows login, you won't get the Windows username. The reason is because when the connection is first established with SQL Server, SQL Server asks for the credentials (the login). Either a hash representing a Windows login is sent (if the connection is being made using Windows/Integrated authentication) or the username and the password (using a weak form of encryption) is sent. This is an either/or situation.
Therefore, if the program connects using a SQL Server login for all users, you won't be able to tell, at least through SQL Server, who the Windows user is on the other end. At least not through sysprocesses, sp_who, sp_who2, etc. If you program passes that information, say as a parameter of a stored procedure, you would, but not through the actual login to SQL Server.
K. Brian Kelley
@kbriankelley
October 4, 2007 at 8:05 am
I am getting the nt_username on all logins except for the two new computers.
Howard
October 4, 2007 at 8:53 am
Then those two are using SQL Server logins to connect. The others must be using Windows logins. If there is an ODBC connection, you may check its configuration.
K. Brian Kelley
@kbriankelley
Viewing 15 posts - 1 through 15 (of 24 total)
You must be logged in to reply to this topic. Login to reply