?
Solved

SQL Server 2008 restricted user access

Posted on 2013-05-27
3
Medium Priority
?
602 Views
Last Modified: 2013-05-28
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?
0
Comment
Question by:grwallace
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 2000 total points
ID: 39199798
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
 

Author Comment

by:grwallace
ID: 39200542
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
 

Author Closing Comment

by:grwallace
ID: 39200852
I got it to work once I sorted out the password issue

Thanks
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Ready to get certified? Check out some courses that help you prepare for third-party exams.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

765 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question