October 29, 2015 at 1:57 pm
Hi everyone.
Hope someone can offer some advice. Here is my scenario.
I have a SQL 2008 server , that has a database. The application users are in the database , but when I look at them in SSMS. I see the user (one I am concerned with) with a red arrow meaning I think it has the Connect permission revoked. I get that.
I then look at the SQL logins , and I don't see a login I was expecting to match user.
But when I look at Activity monitor I see the login connecting.. and I can recreate that activity when I test the small web application that the logins and user are associated with.
When I run
SELECT name, hasdbaccess FROM sys.sysusers WHERE name = 'ABC\123'
I get
namehasdbaccess
ABC\1230
which I read as ABC\123 has no access , but I can see the login in activity monitor.
I have asked the devs to supply the full connection string.
Can anyone explain what I am seeing and how I can address it and demo the impact to the devs ?
cheers for any advice.
October 29, 2015 at 2:05 pm
Login Names and User Names do not have to match , usually when you create a login and then map the login to databases a user with the same name is created automatically , however on occasion a database might have logins created first and then users created later at which point the developer can simply bind the user to a different login.
If the Login is disabled (red arrow pointing down) applications cannot connect to the database using it. So all you need to do is go to the properties window of the user and look under General to find out which login (if any) the user is bound to
EDIT :- Since the post was in SQL 2008 I assume your not using contained users , which is a whole different story
October 29, 2015 at 2:24 pm
Jayanth_Kurup (10/29/2015)
Login Names and User Names do not have to match , usually when you create a login and then map the login to databases a user with the same name is created automatically , however on occasion a database might have logins created first and then users created later at which point the developer can simply bind the user to a different login.If the Login is disabled (red arrow pointing down) applications cannot connect to the database using it. So all you need to do is go to the properties window of the user and look under General to find out which login (if any) the user is bound to
EDIT :- Since the post was in SQL 2008 I assume your not using contained users , which is a whole different story
Ok thanks for that , when I go to the properties I see the details for login and user being the same.
if they are the same , but no login exists how do I tell how the connection is being made ?
October 29, 2015 at 3:05 pm
Check what domain groups or local Windows admin role may have been granted login and access, and then check if this user is a member of that domain group. Users can login by virtue of their domain or windows group membership too.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
October 29, 2015 at 6:38 pm
Eric M Russell (10/29/2015)
Check what domain groups or local Windows admin role may have been granted login and access, and then check if this user is a member of that domain group. Users can login by virtue of their domain or windows group membership too.
I have to ask if you (as yourself) have permission to see the logins. It sounds like you do, but I figure it's worth asking.
From what you've said thus far, my gut tells me that you're dealing with Windows AD group permissions. I genuinely hope the login isn't in the Domain Admins group. :w00t:
October 30, 2015 at 1:29 am
MickyD (10/29/2015)
Jayanth_Kurup (10/29/2015)
Login Names and User Names do not have to match , usually when you create a login and then map the login to databases a user with the same name is created automatically , however on occasion a database might have logins created first and then users created later at which point the developer can simply bind the user to a different login.If the Login is disabled (red arrow pointing down) applications cannot connect to the database using it. So all you need to do is go to the properties window of the user and look under General to find out which login (if any) the user is bound to
EDIT :- Since the post was in SQL 2008 I assume your not using contained users , which is a whole different story
Ok thanks for that , when I go to the properties I see the details for login and user being the same.
if they are the same , but no login exists how do I tell how the connection is being made ?
try running profiler to look for that logins connections
October 30, 2015 at 1:49 am
Jayanth_Kurup (10/30/2015)
MickyD (10/29/2015)
Jayanth_Kurup (10/29/2015)
Login Names and User Names do not have to match , usually when you create a login and then map the login to databases a user with the same name is created automatically , however on occasion a database might have logins created first and then users created later at which point the developer can simply bind the user to a different login.If the Login is disabled (red arrow pointing down) applications cannot connect to the database using it. So all you need to do is go to the properties window of the user and look under General to find out which login (if any) the user is bound to
EDIT :- Since the post was in SQL 2008 I assume your not using contained users , which is a whole different story
Ok thanks for that , when I go to the properties I see the details for login and user being the same.
if they are the same , but no login exists how do I tell how the connection is being made ?
try running profiler to look for that logins connections
Good idea , thanks for the advice everyone. Will take a look into the AD account issue.
October 30, 2015 at 7:11 am
Ed Wagner (10/29/2015)
Eric M Russell (10/29/2015)
Check what domain groups or local Windows admin role may have been granted login and access, and then check if this user is a member of that domain group. Users can login by virtue of their domain or windows group membership too.I have to ask if you (as yourself) have permission to see the logins. It sounds like you do, but I figure it's worth asking.
From what you've said thus far, my gut tells me that you're dealing with Windows AD group permissions. I genuinely hope the login isn't in the Domain Admins group. :w00t:
I personally wouldn't add a login for the Domain Admin or Local (building) Administrators; I'm just asking if that's what's going on for the OP.
There is an extended procedure call for listing members of domain groups.
https://msdn.microsoft.com/en-us/library/ms190369.aspx
For example:
EXEC xp_logininfo 'MYCORP\DBAProduction', 'members';
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
October 30, 2015 at 9:06 am
Eric M Russell (10/30/2015)
Ed Wagner (10/29/2015)
Eric M Russell (10/29/2015)
Check what domain groups or local Windows admin role may have been granted login and access, and then check if this user is a member of that domain group. Users can login by virtue of their domain or windows group membership too.I have to ask if you (as yourself) have permission to see the logins. It sounds like you do, but I figure it's worth asking.
From what you've said thus far, my gut tells me that you're dealing with Windows AD group permissions. I genuinely hope the login isn't in the Domain Admins group. :w00t:
I personally wouldn't add a login for the Domain Admin or Local (building) Administrators; I'm just asking if that's what's going on for the OP.
Neither would I, but you might be on to something as to what's going on with his login that he can't track down.
November 3, 2015 at 1:54 am
Thanks for replies everyone , done some more investigation and the situation does not get any clearer.....
Ok this is what I found. To recap.
User in databases has red arrow. - connect permission revoked I understand
No login exists for User on server.
User can connect and connection seen in dummy web app. I can see login making connection , and where it is coming from.
User (although still connection permission revoked) is a member of Db_owner when user details checked.
When this permission is removed. Login fails.
When back in , login works.
Had a look at AD groups that AD user is a member of and there is nothing extra-ordinary about his permission set.
So my question still is how is this user connecting with no login and user account has connect revoked ?
Odd.....:w00t:
November 3, 2015 at 5:17 am
Is there any way to disable a user in visual way?
I cant see red arrow even if I remove all role memberships for a user.
___________________________________
Computer Enterprise Masoud Keshavarz
I don't care about hell.
If I go there I've played enough Diablo to know how to fight my way out.
November 3, 2015 at 7:34 am
The domains that the user is a member of, have any of these domains been granted a login? Also, if this user is a member of IT in any capacity (developer, manager, help desk), then it's possible that at some point in the past there were added (or added themselves) as members of a local Admin group (ex: BUILTIN\Administrators ) in the box hosting SQL Server, and often times these local Admin account are members of SQL Server sysadmin role. That default install feature hasn't existed since v2005 I think, but if this v2008 instance was upgraded from an older v2005 instance, then it may have inherited some of these unnecessary logins.
Use the technique described here or something similar to determine if they are somehow a member of a local admin group.
Identify Local Administrators on a SQL Server box using PowerShell
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply