Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

login_name shows up as blank in sys.dm_exec_sessions but not in sp_who2 Expand / Collapse
Author
Message
Posted Wednesday, August 31, 2011 8:20 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Friday, October 17, 2014 1:18 PM
Points: 10,255, Visits: 13,218
Marios Philippopoulos (8/31/2011)
Jack Corbett (8/31/2011)
Posted this on Twitter and got an interesting question. Are you seeing this happen with a specific application like Access?


I get it for something called "Entity Framework", but I don't know much else about it. I would need to ask the devs about that.
We don't use MS Access on that server.
I also just saw a linked server query running on the server in question, linking to another server; login name was blank for that session as well.

Unfortunately that's all I have at the moment.


Okay, Entity Framework is MS's latest and greatest data access tool or ORM tool. I haven't worked much with Entity Framework applications so I haven't seen it. If I have a chance I'll do a quick EF app and see what happens.




Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #1168136
Posted Wednesday, August 31, 2011 8:23 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Wednesday, October 8, 2014 12:34 PM
Points: 1,865, Visits: 3,615
Jack Corbett (8/31/2011)
Marios Philippopoulos (8/31/2011)
Jack Corbett (8/31/2011)
Posted this on Twitter and got an interesting question. Are you seeing this happen with a specific application like Access?


I get it for something called "Entity Framework", but I don't know much else about it. I would need to ask the devs about that.
We don't use MS Access on that server.
I also just saw a linked server query running on the server in question, linking to another server; login name was blank for that session as well.

Unfortunately that's all I have at the moment.


Okay, Entity Framework is MS's latest and greatest data access tool or ORM tool. I haven't worked much with Entity Framework applications so I haven't seen it. If I have a chance I'll do a quick EF app and see what happens.


Great, I hadn't realized it was something so widely known and used.
I will certainly keep an eye out for it.


__________________________________________________________________________________

Turbocharge Your Database Maintenance With Service Broker: Part 2
Turbocharge Your Database Maintenance With Service Broker: Part 1
Real-Time Tracking of Tempdb Utilization Through Reporting Services
Monitoring Database Blocking Through SCOM 2007 Custom Rules and Alerts
Preparing for the Unthinkable - a Disaster/Recovery Implementation
Post #1168141
Posted Wednesday, August 31, 2011 3:05 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Wednesday, October 8, 2014 12:34 PM
Points: 1,865, Visits: 3,615
I just did a quick check.

There is a session running EntityFramework, and the original_login_name is populated (not NULL); login_name is blank.

I noticed that there are 3 threads for that session: 3 records returned. Why would that be?

I am able to get most other information for that session, such as SQL running, query plan etc.


__________________________________________________________________________________

Turbocharge Your Database Maintenance With Service Broker: Part 2
Turbocharge Your Database Maintenance With Service Broker: Part 1
Real-Time Tracking of Tempdb Utilization Through Reporting Services
Monitoring Database Blocking Through SCOM 2007 Custom Rules and Alerts
Preparing for the Unthinkable - a Disaster/Recovery Implementation
Post #1168392
Posted Wednesday, August 31, 2011 3:09 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, September 25, 2014 9:01 PM
Points: 1,113, Visits: 705
Marios Philippopoulos (8/31/2011)

I noticed that there are 3 threads for that session: 3 records returned. Why would that be?


3 threads? You're not joining to any views that deal with threads. Do each of the three rows have different request IDs? If so, EF is making use of MARS (Multiple Active Result Sets). If not, then something odd is going on or you're using a different version of the query than the one you posted.


--
Adam Machanic
SQL Server MVP
SQLblog.com: THE SQL Server Blog Spot on the Web
Post #1168395
Posted Wednesday, August 31, 2011 3:18 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Wednesday, October 8, 2014 12:34 PM
Points: 1,865, Visits: 3,615
Adam Machanic (8/31/2011)
Marios Philippopoulos (8/31/2011)

I noticed that there are 3 threads for that session: 3 records returned. Why would that be?


3 threads? You're not joining to any views that deal with threads. Do each of the three rows have different request IDs? If so, EF is making use of MARS (Multiple Active Result Sets). If not, then something odd is going on or you're using a different version of the query than the one you posted.


I shouldn't have used the term "threads"; I just meant three records returned for the same session_id.
I will add request_id to the list of columns returned by the query to check for MARS.


__________________________________________________________________________________

Turbocharge Your Database Maintenance With Service Broker: Part 2
Turbocharge Your Database Maintenance With Service Broker: Part 1
Real-Time Tracking of Tempdb Utilization Through Reporting Services
Monitoring Database Blocking Through SCOM 2007 Custom Rules and Alerts
Preparing for the Unthinkable - a Disaster/Recovery Implementation
Post #1168399
Posted Wednesday, August 31, 2011 3:31 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Wednesday, October 8, 2014 12:34 PM
Points: 1,865, Visits: 3,615
Here is another case of login name showing up as blank (not from EntityFramework).

2 records returned with:

same session_id (=163)
same request_id (=1)
status='running'
login_name BLANK
original_login_name = 'some value'
program_name = 'Microsoft SQL Server'
wait_type = 'OLEDB'

Why would there be 2 records returned for this?

This query originates from another server through a linked server.


Again, here is the SQL I am running:
SELECT
[des].session_id
, [der].request_id
, [des].[status]
, [des].login_name
, [des].original_login_name
, [des].[host_name]
, der.blocking_session_id
, DB_NAME(der.database_id) AS database_name
, der.command
, [des].cpu_time
, [des].reads
, [des].writes
, [dec].last_write
, [des].[program_name]
, der.wait_type
, der.wait_time
, der.last_wait_type
, der.wait_resource
, CASE [des].transaction_isolation_level
WHEN 0 THEN 'Unspecified'
WHEN 1 THEN 'ReadUncommitted'
WHEN 2 THEN 'ReadCommitted'
WHEN 3 THEN 'Repeatable'
WHEN 4 THEN 'Serializable'
WHEN 5 THEN 'Snapshot'
END AS transaction_isolation_level
, OBJECT_NAME( dest.objectid, der.database_id ) AS [object_name]
, dest.[text] AS [executing batch]
, SUBSTRING(
dest.[text], der.statement_start_offset / 2,
( CASE WHEN der.statement_end_offset = -1 THEN DATALENGTH (dest.[text])
ELSE der.statement_end_offset
END - der.statement_start_offset ) / 2
) AS [executing statement]
--, deqp.query_plan
FROM
sys.dm_exec_sessions [des]
LEFT JOIN
sys.dm_exec_requests der
ON
[des].session_id = der.session_id
LEFT JOIN
sys.dm_exec_connections [dec]
ON
[des].session_id = [dec].session_id
OUTER APPLY
sys.dm_exec_sql_text(der.sql_handle) dest
--OUTER APPLY
-- sys.dm_exec_query_plan(der.plan_handle) deqp
WHERE
[des].session_id <> @@SPID
AND [des].[status] <> 'sleeping'
ORDER BY
[des].session_id;



__________________________________________________________________________________

Turbocharge Your Database Maintenance With Service Broker: Part 2
Turbocharge Your Database Maintenance With Service Broker: Part 1
Real-Time Tracking of Tempdb Utilization Through Reporting Services
Monitoring Database Blocking Through SCOM 2007 Custom Rules and Alerts
Preparing for the Unthinkable - a Disaster/Recovery Implementation
Post #1168404
Posted Thursday, February 28, 2013 6:17 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, October 15, 2014 11:25 AM
Points: 15, Visits: 295

I have a very similar issue.
I am developing a monitoring tool,
And there is an incident I cannot monitor.

One of my users logged into an application to insert data into the database.
I can see his machine name, but I cannot see his name.

His login_name shows S-1-9-3-322691058-1310504681-3474585482-2036716736.
This comes from sys.dm_exec_sessions login_name column.
I am looking for a way to find the user name, if I find something I will post it here.

I have tried many ways from inside SQL Server.
select * from sys.server_principals
where sid = 'S-1-9-3-322691058-1310504681-3474585482-2036716736'

select suser_name(S-1-9-3-322691058-1310504681-3474585482-2036716736)


--select suser_name()

sp_helplogins

SELECT * FROM master.dbo.sysusers
where sid = 'S-1-9-3-322691058-1310504681-3474585482-2036716736'

SELECT * FROM master.dbo.syslogins
where sid = 'S-1-9-3-322691058-1310504681-3474585482-2036716736'

Post #1425018
Posted Friday, September 6, 2013 1:15 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, May 19, 2014 12:39 PM
Points: 2, Visits: 87
Try:

SELECT original_login_name, *
FROM sys.dm_exec_sessions
where login_name = 'S-1-9-3-322691058-1310504681-3474585482-2036716736'

I got the same issue, but do not know how to fix it so SQL Server shows the correct login name or SID. So does anyone know if the issue is directly from SQL Server, AD, or from the .NET application?
Post #1492389
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse