Solved

SQL Server 2008 restricted user access

Posted on 2013-05-27
3
591 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
  • 2
3 Comments
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 500 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

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

707 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now