Get the windows user logged on a machine using SQL Server
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty height:90px;width:728px;box-sizing:border-box;
I have a SQL Agent job that creates a list of database users to the managers, monthly.
For SQL Authenticated Users, they want to know the username of the person/process logged on the machine from which SQL database is being hit. I know that I can get the host_name using sys.dm_exec_sessions. Would anyone happen to have a powershell script to get the user/process of that host, or any other idea on how this can be achieved?
sql-server powershell edited Nov 24 '18 at 12:58
marc_s
586k13011261272
asked Nov 15 '18 at 14:43
JayJay
1037
add a comment |
I have a SQL Agent job that creates a list of database users to the managers, monthly.
For SQL Authenticated Users, they want to know the username of the person/process logged on the machine from which SQL database is being hit. I know that I can get the host_name using sys.dm_exec_sessions. Would anyone happen to have a powershell script to get the user/process of that host, or any other idea on how this can be achieved?
sql-server powershell edited Nov 24 '18 at 12:58
marc_s
586k13011261272
asked Nov 15 '18 at 14:43
JayJay
1037
((gwmi win32_process -filter "processid=14892").GetOwner()).User. But you'll probably need remoting to get that to work on the machine itself (Invoke-Command) -- you can use-comp <host>to do the WMI query remotely, but even with remote WMI enabled I'm pretty sure retrieving the user this way won't work.
– Jeroen Mostert
Nov 15 '18 at 15:00
add a comment |
I have a SQL Agent job that creates a list of database users to the managers, monthly.
For SQL Authenticated Users, they want to know the username of the person/process logged on the machine from which SQL database is being hit. I know that I can get the host_name using sys.dm_exec_sessions. Would anyone happen to have a powershell script to get the user/process of that host, or any other idea on how this can be achieved?
sql-server powershell edited Nov 24 '18 at 12:58
marc_s
586k13011261272
asked Nov 15 '18 at 14:43
JayJay
1037
I have a SQL Agent job that creates a list of database users to the managers, monthly.
For SQL Authenticated Users, they want to know the username of the person/process logged on the machine from which SQL database is being hit. I know that I can get the host_name using sys.dm_exec_sessions. Would anyone happen to have a powershell script to get the user/process of that host, or any other idea on how this can be achieved?
sql-server powershell sql-server powershell edited Nov 24 '18 at 12:58
marc_s
586k13011261272
asked Nov 15 '18 at 14:43
JayJay
1037
edited Nov 24 '18 at 12:58
marc_s
586k13011261272
asked Nov 15 '18 at 14:43
JayJay
1037
edited Nov 24 '18 at 12:58
marc_s
586k13011261272
edited Nov 24 '18 at 12:58
marc_s
586k13011261272
edited Nov 24 '18 at 12:58
marc_s
586k13011261272
586k13011261272
asked Nov 15 '18 at 14:43
JayJay
1037
asked Nov 15 '18 at 14:43
JayJay
1037
asked Nov 15 '18 at 14:43
JayJay
1037
1037
((gwmi win32_process -filter "processid=14892").GetOwner()).User. But you'll probably need remoting to get that to work on the machine itself (Invoke-Command) -- you can use-comp <host>to do the WMI query remotely, but even with remote WMI enabled I'm pretty sure retrieving the user this way won't work.
– Jeroen Mostert
Nov 15 '18 at 15:00
add a comment |
((gwmi win32_process -filter "processid=14892").GetOwner()).User. But you'll probably need remoting to get that to work on the machine itself (Invoke-Command) -- you can use-comp <host>to do the WMI query remotely, but even with remote WMI enabled I'm pretty sure retrieving the user this way won't work.
– Jeroen Mostert
Nov 15 '18 at 15:00
((gwmi win32_process -filter "processid=14892").GetOwner()).User. But you'll probably need remoting to get that to work on the machine itself (Invoke-Command) -- you can use -comp <host> to do the WMI query remotely, but even with remote WMI enabled I'm pretty sure retrieving the user this way won't work.– Jeroen Mostert
Nov 15 '18 at 15:00
((gwmi win32_process -filter "processid=14892").GetOwner()).User. But you'll probably need remoting to get that to work on the machine itself (Invoke-Command) -- you can use -comp <host> to do the WMI query remotely, but even with remote WMI enabled I'm pretty sure retrieving the user this way won't work.– Jeroen Mostert
Nov 15 '18 at 15:00
add a comment |
2 Answers
2
active
oldest
votes
AFAIK there is no link between a SQL login and an NT user. Backtracking with hostname is your best bet.
answered Nov 15 '18 at 14:48
elkenelken
138110
1
I agree, I can get host_name using sys.dm_exec_sessions in SQL Server. Is there a script in handy that I could use to get the NT_User from host name?
– Jay
Nov 15 '18 at 14:50
@JayGet-WmiObject –ComputerName <machine name> –Class Win32_ComputerSystem | Select-Object UserName
– elken
Nov 15 '18 at 14:58
add a comment |
Below query will fetch you the Username and the machine details:-
SELECT HOST_NAME() AS HostName, SUSER_NAME() LoggedInUser
answered Nov 15 '18 at 14:53
Akash VermaAkash Verma
2529
This gives the host name of the current machine and the current user. I need to find out the NT_Username of the person who was logged on at a particular time on a particular machine?
– Jay
Nov 15 '18 at 15:00
add a comment |
Your Answer
StackExchange.ifUsing("editor", function ()
StackExchange.using("externalEditor", function ()
StackExchange.using("snippets", function ()
StackExchange.snippets.init();
);
);
, "code-snippets");
StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "1"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);
else
createEditor();
);
function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);
);
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53321938%2fget-the-windows-user-logged-on-a-machine-using-sql-server%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
AFAIK there is no link between a SQL login and an NT user. Backtracking with hostname is your best bet.
answered Nov 15 '18 at 14:48
elkenelken
138110
1
I agree, I can get host_name using sys.dm_exec_sessions in SQL Server. Is there a script in handy that I could use to get the NT_User from host name?
– Jay
Nov 15 '18 at 14:50
@JayGet-WmiObject –ComputerName <machine name> –Class Win32_ComputerSystem | Select-Object UserName
– elken
Nov 15 '18 at 14:58
add a comment |
AFAIK there is no link between a SQL login and an NT user. Backtracking with hostname is your best bet.
answered Nov 15 '18 at 14:48
elkenelken
138110
1
I agree, I can get host_name using sys.dm_exec_sessions in SQL Server. Is there a script in handy that I could use to get the NT_User from host name?
– Jay
Nov 15 '18 at 14:50
@JayGet-WmiObject –ComputerName <machine name> –Class Win32_ComputerSystem | Select-Object UserName
– elken
Nov 15 '18 at 14:58
add a comment |
AFAIK there is no link between a SQL login and an NT user. Backtracking with hostname is your best bet.
answered Nov 15 '18 at 14:48
elkenelken
138110
AFAIK there is no link between a SQL login and an NT user. Backtracking with hostname is your best bet.
answered Nov 15 '18 at 14:48
elkenelken
138110
answered Nov 15 '18 at 14:48
elkenelken
138110
answered Nov 15 '18 at 14:48
elkenelken
138110
answered Nov 15 '18 at 14:48
elkenelken
138110
138110
1
I agree, I can get host_name using sys.dm_exec_sessions in SQL Server. Is there a script in handy that I could use to get the NT_User from host name?
– Jay
Nov 15 '18 at 14:50
@JayGet-WmiObject –ComputerName <machine name> –Class Win32_ComputerSystem | Select-Object UserName
– elken
Nov 15 '18 at 14:58
add a comment |
1
I agree, I can get host_name using sys.dm_exec_sessions in SQL Server. Is there a script in handy that I could use to get the NT_User from host name?
– Jay
Nov 15 '18 at 14:50
@JayGet-WmiObject –ComputerName <machine name> –Class Win32_ComputerSystem | Select-Object UserName
– elken
Nov 15 '18 at 14:58
1
1
I agree, I can get host_name using sys.dm_exec_sessions in SQL Server. Is there a script in handy that I could use to get the NT_User from host name?
– Jay
Nov 15 '18 at 14:50
I agree, I can get host_name using sys.dm_exec_sessions in SQL Server. Is there a script in handy that I could use to get the NT_User from host name?
– Jay
Nov 15 '18 at 14:50
@Jay
Get-WmiObject –ComputerName <machine name> –Class Win32_ComputerSystem | Select-Object UserName– elken
Nov 15 '18 at 14:58
@Jay
Get-WmiObject –ComputerName <machine name> –Class Win32_ComputerSystem | Select-Object UserName– elken
Nov 15 '18 at 14:58
add a comment |
Below query will fetch you the Username and the machine details:-
SELECT HOST_NAME() AS HostName, SUSER_NAME() LoggedInUser
answered Nov 15 '18 at 14:53
Akash VermaAkash Verma
2529
This gives the host name of the current machine and the current user. I need to find out the NT_Username of the person who was logged on at a particular time on a particular machine?
– Jay
Nov 15 '18 at 15:00
add a comment |
Below query will fetch you the Username and the machine details:-
SELECT HOST_NAME() AS HostName, SUSER_NAME() LoggedInUser
answered Nov 15 '18 at 14:53
Akash VermaAkash Verma
2529
This gives the host name of the current machine and the current user. I need to find out the NT_Username of the person who was logged on at a particular time on a particular machine?
– Jay
Nov 15 '18 at 15:00
add a comment |
Below query will fetch you the Username and the machine details:-
SELECT HOST_NAME() AS HostName, SUSER_NAME() LoggedInUser
answered Nov 15 '18 at 14:53
Akash VermaAkash Verma
2529
Below query will fetch you the Username and the machine details:-
SELECT HOST_NAME() AS HostName, SUSER_NAME() LoggedInUser
answered Nov 15 '18 at 14:53
Akash VermaAkash Verma
2529
answered Nov 15 '18 at 14:53
Akash VermaAkash Verma
2529
answered Nov 15 '18 at 14:53
Akash VermaAkash Verma
2529
answered Nov 15 '18 at 14:53
Akash VermaAkash Verma
2529
2529
This gives the host name of the current machine and the current user. I need to find out the NT_Username of the person who was logged on at a particular time on a particular machine?
– Jay
Nov 15 '18 at 15:00
add a comment |
This gives the host name of the current machine and the current user. I need to find out the NT_Username of the person who was logged on at a particular time on a particular machine?
– Jay
Nov 15 '18 at 15:00
This gives the host name of the current machine and the current user. I need to find out the NT_Username of the person who was logged on at a particular time on a particular machine?
– Jay
Nov 15 '18 at 15:00
This gives the host name of the current machine and the current user. I need to find out the NT_Username of the person who was logged on at a particular time on a particular machine?
– Jay
Nov 15 '18 at 15:00
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53321938%2fget-the-windows-user-logged-on-a-machine-using-sql-server%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
((gwmi win32_process -filter "processid=14892").GetOwner()).User. But you'll probably need remoting to get that to work on the machine itself (Invoke-Command) -- you can use-comp <host>to do the WMI query remotely, but even with remote WMI enabled I'm pretty sure retrieving the user this way won't work.– Jeroen Mostert
Nov 15 '18 at 15:00