SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


login_name shows up as blank in sys.dm_exec_sessions but not in sp_who2


login_name shows up as blank in sys.dm_exec_sessions but not in sp_who2

Author
Message
Jack Corbett
  Jack Corbett
SSCoach
SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)

Group: General Forum Members
Points: 19082 Visits: 14900
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
At best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at work

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
Marios Philippopoulos
Marios Philippopoulos
SSCarpal Tunnel
SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)

Group: General Forum Members
Points: 4738 Visits: 3758
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.

__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables
Persisting SQL Server Index-Usage Statistics with MERGE
Turbocharge Your Database Maintenance With Service Broker: Part 2
Marios Philippopoulos
Marios Philippopoulos
SSCarpal Tunnel
SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)

Group: General Forum Members
Points: 4738 Visits: 3758
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.

__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables
Persisting SQL Server Index-Usage Statistics with MERGE
Turbocharge Your Database Maintenance With Service Broker: Part 2
Adam Machanic
Adam Machanic
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1609 Visits: 714
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
Marios Philippopoulos
Marios Philippopoulos
SSCarpal Tunnel
SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)

Group: General Forum Members
Points: 4738 Visits: 3758
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.

__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables
Persisting SQL Server Index-Usage Statistics with MERGE
Turbocharge Your Database Maintenance With Service Broker: Part 2
Marios Philippopoulos
Marios Philippopoulos
SSCarpal Tunnel
SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)

Group: General Forum Members
Points: 4738 Visits: 3758
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;



__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables
Persisting SQL Server Index-Usage Statistics with MERGE
Turbocharge Your Database Maintenance With Service Broker: Part 2
marcelo miorelli
marcelo miorelli
SSC Rookie
SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)

Group: General Forum Members
Points: 47 Visits: 399
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'
kjarr
kjarr
Grasshopper
Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)

Group: General Forum Members
Points: 10 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?
jaminbw
jaminbw
SSC Rookie
SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)

Group: General Forum Members
Points: 37 Visits: 403
I am seeing this also on a SSIS package when looking in master..sysprocesses.login I get a value such as S-1-9-4090058726-290700479-3335545161
Is this from login context shift such as a execute as login?

How can we map this to a user?
Jack Corbett
  Jack Corbett
SSCoach
SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)

Group: General Forum Members
Points: 19082 Visits: 14900
Just saw this recently when using a loginless user using EXECUTE AS. Could be that this is what is happening.

You can verify using this:


CREATE USER test WITHOUT LOGIN;
GO

EXECUTE AS USER = 'test';

SELECT * FROM sys.all_columns AS AC;

WAITFOR DELAY '00:00:30';

REVERT;



And run this in a another query window:


SELECT original_login_name, *
FROM sys.dm_exec_sessions





Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming
At best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at work

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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search