[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

SQL Server 2008 restricted user access

Posted on 2013-05-27
3
Medium Priority
?
607 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

Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

Question has a verified solution.

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

Ready to get certified? Check out some courses that help you prepare for third-party exams.
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

656 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