August 31, 2011 at 3:31 pm
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[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
February 28, 2013 at 6:17 am
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'
September 6, 2013 at 1:15 pm
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?
July 15, 2015 at 12:52 pm
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?
July 15, 2015 at 1:06 pm
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
Consultant - Straight Path Solutions
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
August 2, 2018 at 10:36 am
Jack,
I see the test login show up with the simpler query select login_name,count(session_id) as sescnt from sys.dm_exec_sessions group by login_name
Still see a blank. We do have an entity framework connecting to our network.
Did the blank get positively identified as EF?
Jamie
Jamie
Viewing 6 posts - 16 through 20 (of 20 total)
You must be logged in to reply to this topic. Login to reply