sql server 2008 permissions for user connecting from another machine on same network not as sysadmin

I have 2 machines and 2 users for MS SQL server 2008 R2
Both machines belong to the same domain network.

machine A is a windows 7 pro pc trying to setup an odbc connection to machine B ( sql server)running
machine B windows 2008 r2 server running sql server

I am setting up an odbc on machine a to access machine b
       If i setup the connection as "sa" the connection works fine.
       If i setup the connection as "myuser" i cant connect to the server getting error

As a suggestion i tried adding "sysadmin" role to "myuser" and i am able to connect ok, but i dont think this is the right way to do it. Looking to see what actually needs to be setup for myuser to be able to connect not as a sysadmin.
Extreme66Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Beth KludtIndependent Business OwnerCommented:
Did you grant "myuser" access to the target database in addition to creating its login?
0
Extreme66Author Commented:
Yes, thanks
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
What's the message error?
0
Problems using Powershell and Active Directory?

Managing Active Directory does not always have to be complicated.  If you are spending more time trying instead of doing, then it's time to look at something else. For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why

Extreme66Author Commented:
I should have put that in already, thanks
Connection failed:
SQLState: '28000'
SQL Server Error: 18456
[Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user 'myuser'.

I am able to login to enterprise manager with the myuser id however and see my databases.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Enterprise Manager?
Shouldn't be Management Studio?
Enterprise Manager is from SQL Server 2000 and can't connect to superior versions of SQL Server.
0
Extreme66Author Commented:
Sorry had old terminology in my head, yes its Microsoft SQL Management Studio
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
The error appears when you create the DSN in ODBC?
And which permissions has that user in the SQL Server instance?
0
Extreme66Author Commented:
Do you mean sql server permissions or windows permissions?
server roles -> public
user mapping -> db_datareader and public
status grant and enabled
no securables
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Apparently you have it well configured.
Can't figure out from here. Sorry.
0
Extreme66Author Commented:
thanks for the help but i actually figured it out, after server hardening appearantly i needed to add this final grant.

GRANT CONNECT ON ENDPOINT::"TSQL Default TCP" TO "myuser"
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Extreme66Author Commented:
Hopefully this solution finds others, as it would have saved me TONS of time due to an non descriptive error
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Windows Server 2008

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.