March 16, 2018 at 12:49 pm
I am trying to find out which of the logged on users are sysadmin, what am I doing wrong, the result is either 0 or null
SELECT getdate() as datetime,ec.client_net_address, es.[program_name], es.[host_name], es.login_name, DB_NAME(eS.database_id) db_name, es.original_login_name,IS_SRVROLEMEMBER('sysadmin',es.login_name) IsSysadmin
FROM sys.dm_exec_sessions AS es WITH (NOLOCK)
INNER JOIN sys.dm_exec_connections AS ec WITH (NOLOCK)
ON es.session_id = ec.session_id
ORDER BY ec.client_net_address, es.[program_name] OPTION (RECOMPILE
March 16, 2018 at 2:04 pm
goher2000 - Friday, March 16, 2018 12:49 PMI am trying to find out which of the logged on users are sysadmin, what am I doing wrong, the result is either 0 or null
SELECT getdate() as datetime,ec.client_net_address, es.[program_name], es.[host_name], es.login_name, DB_NAME(eS.database_id) db_name, es.original_login_name,IS_SRVROLEMEMBER('sysadmin',es.login_name) IsSysadmin
FROM sys.dm_exec_sessions AS es WITH (NOLOCK)
INNER JOIN sys.dm_exec_connections AS ec WITH (NOLOCK)
ON es.session_id = ec.session_id
ORDER BY ec.client_net_address, es.[program_name] OPTION (RECOMPILE
It works fine for me - I added the closing parenthesis on Recompile but that was just left off...you'd get an error if that's what you ran.
I tried a few variations and it was fine. Even with an account that only has view server state, SQL and Windows, etc. And all on 2014.
Doesn't make sense. Are you manually running this in SSMS?
Sue
March 16, 2018 at 2:44 pm
Yes I am manually running in SSMS, I am in windows group which has sysadmin, yet the query shows null in 'IsSysadmin' column, I plan to create a notification if unauthorized sysadmin log on to system
March 16, 2018 at 2:52 pm
goher2000 - Friday, March 16, 2018 2:44 PMYes I am manually running in SSMS, I am in windows group which has sysadmin, yet the query shows null in 'IsSysadmin' column, I plan to create a notification if unauthorized sysadmin log on to system
Maybe it's due to being in a group? That would be my guess. Didn't play with that one.
Sue
March 16, 2018 at 2:58 pm
Sue_H - Friday, March 16, 2018 2:52 PMgoher2000 - Friday, March 16, 2018 2:44 PMYes I am manually running in SSMS, I am in windows group which has sysadmin, yet the query shows null in 'IsSysadmin' column, I plan to create a notification if unauthorized sysadmin log on to systemMaybe it's due to being in a group? That would be my guess. Didn't play with that one.
Sue
That's not it...just tried and it was fine. I'm still playing with it trying to figure out why it's not working for you.
Sue
March 16, 2018 at 3:08 pm
Sue_H - Friday, March 16, 2018 2:58 PMSue_H - Friday, March 16, 2018 2:52 PMgoher2000 - Friday, March 16, 2018 2:44 PMYes I am manually running in SSMS, I am in windows group which has sysadmin, yet the query shows null in 'IsSysadmin' column, I plan to create a notification if unauthorized sysadmin log on to systemMaybe it's due to being in a group? That would be my guess. Didn't play with that one.
Sue
That's not it...just tried and it was fine. I'm still playing with it trying to figure out why it's not working for you.
Sue
I lied...that is it. But now I'm trying to think of how you work around it.
March 16, 2018 at 3:26 pm
goher2000 - Friday, March 16, 2018 2:44 PMYes I am manually running in SSMS, I am in windows group which has sysadmin, yet the query shows null in 'IsSysadmin' column, I plan to create a notification if unauthorized sysadmin log on to system
Let's stop and think about that. How will someone have sysadmin privs if they weren't given sysadmin privs either by the group they belong to or directly? A hacker couldn't do it unless they got in as an existing login, in which case you'd never see the unauthorized login because they'd be logged in as someone who was authorized as sysadmin.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 16, 2018 at 4:19 pm
Jeff Moden - Friday, March 16, 2018 3:26 PMgoher2000 - Friday, March 16, 2018 2:44 PMYes I am manually running in SSMS, I am in windows group which has sysadmin, yet the query shows null in 'IsSysadmin' column, I plan to create a notification if unauthorized sysadmin log on to systemLet's stop and think about that. How will someone have sysadmin privs if they weren't given sysadmin privs either by the group they belong to or directly? A hacker couldn't do it unless they got in as an existing login, in which case you'd never see the unauthorized login because they'd be logged in as someone who was authorized as sysadmin.
I think gopher2000 is just trying to monitor sysadmin access in the query that was being a problem. I don't think Notifications would work unless tracking elsewhere who has "true" sysadmin access. Personally I'd rather just check the sysadmins on a regular basis. But that query is just trying to see what sysadmins are currently connected.
There are some Powershell scripts out there to get tokens from services, manipulating the tokens. Similar thing done with psexec. So if you are an admin on the server...you can get in without hacking a login or being given sysadmin access. Or restarting, single user mode, etc. Once in create logins, add to sysadmins....
Sue
March 16, 2018 at 5:06 pm
Sue_H - Friday, March 16, 2018 4:19 PMJeff Moden - Friday, March 16, 2018 3:26 PMgoher2000 - Friday, March 16, 2018 2:44 PMYes I am manually running in SSMS, I am in windows group which has sysadmin, yet the query shows null in 'IsSysadmin' column, I plan to create a notification if unauthorized sysadmin log on to systemLet's stop and think about that. How will someone have sysadmin privs if they weren't given sysadmin privs either by the group they belong to or directly? A hacker couldn't do it unless they got in as an existing login, in which case you'd never see the unauthorized login because they'd be logged in as someone who was authorized as sysadmin.
I think gopher2000 is just trying to monitor sysadmin access in the query that was being a problem. I don't think Notifications would work unless tracking elsewhere who has "true" sysadmin access. Personally I'd rather just check the sysadmins on a regular basis. But that query is just trying to see what sysadmins are currently connected.
There are some Powershell scripts out there to get tokens from services, manipulating the tokens. Similar thing done with psexec. So if you are an admin on the server...you can get in without hacking a login or being given sysadmin access. Or restarting, single user mode, etc. Once in create logins, add to sysadmins....
Sue
I guess I took too much stock in the word "unauthorized" in the OP saying " I plan to create a notification if unauthorized sysadmin log on to system".
You do have my curiosity up... I'm not a good PowerShell user by any means. If I disable the "BUILTIN\ADMINISTRATORS" on SQL Server, won't that squash any attempt made in PowerShell (etc) such as what you mention?
--Jeff Moden
Change is inevitable... Change for the better is not.
March 16, 2018 at 5:53 pm
Jeff Moden - Friday, March 16, 2018 5:06 PMSue_H - Friday, March 16, 2018 4:19 PMJeff Moden - Friday, March 16, 2018 3:26 PMgoher2000 - Friday, March 16, 2018 2:44 PMYes I am manually running in SSMS, I am in windows group which has sysadmin, yet the query shows null in 'IsSysadmin' column, I plan to create a notification if unauthorized sysadmin log on to systemLet's stop and think about that. How will someone have sysadmin privs if they weren't given sysadmin privs either by the group they belong to or directly? A hacker couldn't do it unless they got in as an existing login, in which case you'd never see the unauthorized login because they'd be logged in as someone who was authorized as sysadmin.
I think gopher2000 is just trying to monitor sysadmin access in the query that was being a problem. I don't think Notifications would work unless tracking elsewhere who has "true" sysadmin access. Personally I'd rather just check the sysadmins on a regular basis. But that query is just trying to see what sysadmins are currently connected.
There are some Powershell scripts out there to get tokens from services, manipulating the tokens. Similar thing done with psexec. So if you are an admin on the server...you can get in without hacking a login or being given sysadmin access. Or restarting, single user mode, etc. Once in create logins, add to sysadmins....
Sue
I guess I took too much stock in the word "unauthorized" in the OP saying " I plan to create a notification if unauthorized sysadmin log on to system".
You do have my curiosity up... I'm not a good PowerShell user by any means. If I disable the "BUILTIN\ADMINISTRATORS" on SQL Server, won't that squash any attempt made in PowerShell (etc) such as what you mention?
As I said "I don't think Notifications would work unless tracking elsewhere who has "true" sysadmin access". And I also said I wouldn't do it it that way. I was trying to help gopher with a query for what sysadmins were connected. I think Gopher2000 would have figured that out about the notifications once they got there. That was the later intention as I read it - they were stuck on a query to see what sysadmins were connected. They are in a windows group that is in sysadmins and other sysadmins where showing as NULL. Kind of good to know that IS_SRVROLEMEMBER works that way with groups and to know that's why it was null (or 0).
Disabling BUILTIN\ADMINISTRATORS does not squash all methods with Powershell. As I said "if you are an admin on the server". So if you are an admin on the server why would you do some Powershell hack when BUILTIN\Admins is still enabled. You can just log in. I don't get that other than not leaving a trail with your login.
What I was referring to with Powershell.... There have been ways to use tokens (token stealing) for years to get into things with Windows. And it can be done with the tokens for service accounts that are running whatever services. I haven't tried testing it with managed service accounts but being that they can't log in that might be one of the biggest benefits of using those. For now anyway. There was/is a tool available to use that approach and there is some documentation on it. Search on Incognito tool. It's basically a hacker tool that has grown or morphed into some other hacker tools. The whole thing with token stealing is pretty well known and something done with some pen tests as well. It's not just stealing or emulating a local service account but rather any tokens that would be on that server. Which would include the service accounts.
Sue
March 16, 2018 at 6:02 pm
Thanks guys for your input , lets not get confused by why I need it
March 17, 2018 at 1:58 pm
goher2000 - Friday, March 16, 2018 6:02 PMThanks guys for your input , lets not get confused by why I need it
Without the "Why", there is no need. 😉 Without a correct "Why", there may be bad solutions. :alien:
--Jeff Moden
Change is inevitable... Change for the better is not.
March 20, 2018 at 8:48 am
Sue_H - Friday, March 16, 2018 5:53 PMJeff Moden - Friday, March 16, 2018 5:06 PMSue_H - Friday, March 16, 2018 4:19 PMJeff Moden - Friday, March 16, 2018 3:26 PMgoher2000 - Friday, March 16, 2018 2:44 PMYes I am manually running in SSMS, I am in windows group which has sysadmin, yet the query shows null in 'IsSysadmin' column, I plan to create a notification if unauthorized sysadmin log on to systemLet's stop and think about that. How will someone have sysadmin privs if they weren't given sysadmin privs either by the group they belong to or directly? A hacker couldn't do it unless they got in as an existing login, in which case you'd never see the unauthorized login because they'd be logged in as someone who was authorized as sysadmin.
I think gopher2000 is just trying to monitor sysadmin access in the query that was being a problem. I don't think Notifications would work unless tracking elsewhere who has "true" sysadmin access. Personally I'd rather just check the sysadmins on a regular basis. But that query is just trying to see what sysadmins are currently connected.
There are some Powershell scripts out there to get tokens from services, manipulating the tokens. Similar thing done with psexec. So if you are an admin on the server...you can get in without hacking a login or being given sysadmin access. Or restarting, single user mode, etc. Once in create logins, add to sysadmins....
Sue
I guess I took too much stock in the word "unauthorized" in the OP saying " I plan to create a notification if unauthorized sysadmin log on to system".
You do have my curiosity up... I'm not a good PowerShell user by any means. If I disable the "BUILTIN\ADMINISTRATORS" on SQL Server, won't that squash any attempt made in PowerShell (etc) such as what you mention?
As I said "I don't think Notifications would work unless tracking elsewhere who has "true" sysadmin access". And I also said I wouldn't do it it that way. I was trying to help gopher with a query for what sysadmins were connected. I think Gopher2000 would have figured that out about the notifications once they got there. That was the later intention as I read it - they were stuck on a query to see what sysadmins were connected. They are in a windows group that is in sysadmins and other sysadmins where showing as NULL. Kind of good to know that IS_SRVROLEMEMBER works that way with groups and to know that's why it was null (or 0).
Disabling BUILTIN\ADMINISTRATORS does not squash all methods with Powershell. As I said "if you are an admin on the server". So if you are an admin on the server why would you do some Powershell hack when BUILTIN\Admins is still enabled. You can just log in. I don't get that other than not leaving a trail with your login.
What I was referring to with Powershell.... There have been ways to use tokens (token stealing) for years to get into things with Windows. And it can be done with the tokens for service accounts that are running whatever services. I haven't tried testing it with managed service accounts but being that they can't log in that might be one of the biggest benefits of using those. For now anyway. There was/is a tool available to use that approach and there is some documentation on it. Search on Incognito tool. It's basically a hacker tool that has grown or morphed into some other hacker tools. The whole thing with token stealing is pretty well known and something done with some pen tests as well. It's not just stealing or emulating a local service account but rather any tokens that would be on that server. Which would include the service accounts.Sue
BUILTIN\ADMINISTRATORS has not been a built in sql account since sql server 2005, if its in your instance someone added it there
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
March 20, 2018 at 1:16 pm
Perry Whittle - Tuesday, March 20, 2018 8:48 AMBUILTIN\ADMINISTRATORS has not been a built in sql account since sql server 2005, if its in your instance someone added it there
Interesting. I didn't know that and that brings up a question or two for me. When we did our upgrades from 2005 to 2012 (transfer to new box, as well), I specifically excluded it (at least that's my recollection). When it appeared, I thought it was a part of the normal installation. Since then, we did a simple in-place upgrade to 2016 and the login is there, as well (it obviously came for the ride). I never gave it a second thought until this moment.
I'm thinking I might have some Windows Admins that I'll have to wean off the habit of adding it. 😀
--Jeff Moden
Change is inevitable... Change for the better is not.
March 20, 2018 at 2:02 pm
Ahh in place upgrade may be different, logins wont be deleted during this.
Windows admins can only add this login if they know how to start the instance single user and know the tsql cmds to achieve this.
New installs do not create builtin\admibistrators login
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply