SQL Server 2008 restricted user access

I have a working database but have had request to provide access for a third party application.

I have created a series of SPs to do this, but now I would like to create a user, VhcUser, which can only execute SPs and can only "see" the ones for the application. I would then build a connectionstring with these credentials and give it to the third party.

I believe that the best way to do this woul be to use SQL Server authentication, but I have never done aniything like this before.

Can anyone help?
grwallaceAsked:
Who is Participating?
 
Anthony PerkinsConnect With a Mentor Commented:
1. Create the Login on the server
2. Create the user (UserName) in the database.
3. GRANT EXECUTE ON usp_YourStoredProcedureNameGoesHere TO UserName
4. GRANT VIEW DEFINITION ON dbo.usp_YourStoredProcedureNameGoesHere TO UserName
5. Repeat 3 and 4 for the rest of the Stored Procedures.
0
 
grwallaceAuthor Commented:
Thank you for this

I created the user - VhcUser - in the database and it shows up under logins

I then created the user in the database. While I counldn't see the user on re-trying it said that the user existed.

I then ran the Grants for the first SP and they went through OK.
Ithen tried to create a connection to it using Sql Server authentication,. I got the error message Login Failed for VhcUser.

Is this possibly a password issue, and if so how do I assign a password?
0
 
grwallaceAuthor Commented:
I got it to work once I sorted out the password issue

Thanks
0
All Courses

From novice to tech pro — start learning today.