November 22, 2006 at 5:58 am
Hi..
My SQL Server 2005 server is set up with Mixed Mode Auth. I have removed the BUILTIN\Admin login.
When I try to connect to Mgmt Studio with a domain account (does NOT have local admin privileges) it fails as this login was not set up in SQL security.. this is to be expected.
However, if I add an Active Directory group to SQL logins which the domain login account was previously a member of but is not a member of now, the login is still able to connect. Any ideas why this could happen?? It looks like the login is inheritting the privileges from somewhere else but I am unable to trace where?
I am wondering if the login is cached somewhere.. dont think it should be but not sure?
Would appreciate any info or avenues of investigation people might be able to suggest as to why this is happening.
November 22, 2006 at 8:08 am
did you delete the login from the whole server , or just from each individual database?
November 22, 2006 at 9:19 am
The login is still a domain login but I deleted it from the Active Directory group.When I readded the group as a SQL Server Login, and tried to connect to the server (via Mgmt Studio) directly with the login (which was no longer a member of the active directory group), it connected. I am wondering where it got the priviliges from?? I also checked each of the individual databases to ensure that the data user was removed ( I know that when you remove a login, db users do not automatically go with it), but there is no sign of the db user.
Please let me know if I can provide any further details.
Thanks!!
November 22, 2006 at 9:38 am
is the domain account in question in another AD group with login perms to the server? Did you completely disconnect from the sql box before trying to connect post AD group move?
November 22, 2006 at 10:02 am
I checked to ensure that the login is not a member of any other SQL group. The background to this issue is that we are trying to remove a lot of the logins/groups that we had to keep the security as tight as possible so there are no other AD groups set as a login into SQL Server.
Also, made sure that I had disconnected completely and logged out of SQL. Was thinking of restarting the SQL Server but not sure what benefit this would have.
One other thing I noticed is that the user is a member of a second AD group. For testing purposes, I did the following:
1. I removed the original AD group (where my login was somehow connecting from) as a SQL Login.
2. I removed the user from the second AD group.
3. I added the second AD group as a SQL Login (which now did not contain my login).
4. I tried to connect directly to Mgmt Studio with my login but it failed which is correct and what I would have expected from the first AD group.
This would suggest that the first AD group has somehow more privilges than the second AD group. Because the login does not have a direct login and as I have the BUILTIN\Admin group disabled, the question really is how could the login still connect???
November 22, 2006 at 12:53 pm
hmm.. did you log out of the domain with the Domain account in question? You need to refresh your token..
November 23, 2006 at 8:43 am
Yes, I logged out of the domain.
I am thinking that if there were any other legacy connections with the login, would the new connection with the login inherit these priviliges even tho the login had been removed from the group in the meantime. One of my colleagues here ensures me that the policy is updated every 90 minutes and any new connections with the login would connect with the new priviliges (OR lack of prviliges in this case as the login has been removed from the group).
Anybody know how I would identify if there are any other connections on the domain with this login and also how I could see what priviliges the login has..
November 27, 2006 at 2:50 am
Just to close out on this topic, it seems that the login that we were using was being used by another server on the domain that we were unaware of. This is why the login was inheritting the privileges even when I was removing the login from the SQL group.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply