We help IT Professionals succeed at work.

Active Directory /SQL authentication issue

ryansoto
ryansoto asked
on
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?
Comment
Watch Question

Commented:
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.

Author

Commented:
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
ssis.JPG

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

http://msdn.microsoft.com/en-us/library/aa337083.aspx
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.