October 19, 2018 at 12:01 pm
We provide web based software to companies and host the software on our servers. Each client has it's own database. However there are 2 system databases that I query from other sites from time to time. We recently moved to SQL 2014 from SQL 2000. We assign a specific user for web based queries that uses a role called website_user. When I create a new sproc I grant permission to execute the sproc to that role.
I recently wrote a sproc in a client database to query one of the system databases. When I run in the DB no problem. But when I try to run from a webpage I get an error telling me I don't have permissions set.
I need to set permissions to allow me to SELECT on a table. I usually block all table level queries (SELECT, INSERT, UPDATE, DELETE) for that user. How do I adjust permissions in SSMS to allow for this sproc to run when it comes in from the web? I know it involves changing the table to allow permission on SELECT just don't know where to change it.
Here is the procedure
CREATE PROCEDURE [dbo].[GetNumberSectionsInAssessment]
@JobID int
AS
SET NOCOUNT ON
SELECT
COUNT(*) AS NumSections
FROM
CRI_Support.dbo.CRI_AssessmentSections
WHERE
AssessmentID = (
SELECT TOP 1 AssessmentID
FROM CRI_Support.dbo.CRI_Assessments
WHERE AssessmentCode = (SELECT TestType FROM dbo.Jobs WHERE JobID = @JobID)
)
GO
I will say I think MS screwed the pooch on this one. It was much easier to set permissions in SQL 2000
October 19, 2018 at 1:26 pm
October 19, 2018 at 1:35 pm
Interesting. Thank you
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply