We help IT Professionals succeed at work.

Active Directory /SQL authentication issue

ryansoto asked
I have a single user who connects to SQL (SQL management tool)and gets access denied on a particular server.  
Another user in the same groups as user 1 is fine.

User 1 can access if I put into the local admin on the server
User 2 can access just fine not in local admini

User 1 has multiple AD accounts, when he started as a contractor then moved to perm, etc.
I am tempted to kill all his accounts then start with a fresh one but before I did that I thought I would try to solve it.
Any help?
Watch Question

Your security Logins on SQL is not set correctly

Use SSMS under security look for logins.
There you will find an entry for user one as   domain\user1 but not for user2

Just create it there as a first step.
Then you have to set permissions and roles for this login on all the databases user2 may access.


I'm not a dba but I will try to explain a little better -
User is trying to connect to ssis from his workstation and gets denied.
From my limited knowledge if I put this user into the local admin it works fine, but this is bad.
I checked dcom security as some other articles have stated and the users in the dcom group are domain users.  The DBA team set it up this way, which is valid.  Like I said another user is not in local admin and works fine but not this user.
I checked in SSMS and under logins this user is not specified but domain users is specified.

User2 that works ok is also not listed in SSMS.  I attached the screen shot when the user tries to log into integrations services

Are these users setup in the same OU in AD ?
Here was the answer

Under eliminating Access Denied Message

Users had to be a part of this.  The reason user 1 couldnt and user 2 could....
User1 was connecting through the gui by clicking integration services
user 2 was already connected to SSMS then opened integration from the application window.