User Roles - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

User Roles

Author

Topic

smorty44
Yak Posting Veteran

93 Posts

Posted - 2008-04-03 : 10:43:14

I have a sp that my users need access to run and currently their roles are set up as users with db_datareader, db_datawriter. I'm having trouble getting them access just adding permissions to the tables being used here. What is the best method to allow them access to only the neccessary roles to run this sp?

ALTER PROCEDURE dbo.spi_CallList
(@strAgent nchar(4), @MEA int)

AS

DECLARE @Msg varchar(255)
declare @SQL varchar(4000)

IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = 'qs_CallList1')
DROP VIEW qs_CallList1

IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = 'qs_CallList2')
DROP VIEW qs_CallList2

IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = 'qs_CallList3')
DROP VIEW qs_CallList3

SET @SQL = 'Create view qs_CallList1 as
SELECT top 100 percent tsd_Claim.clinsnum AS [Ins#], Sum(tsd_Claim.cloutstandingamt) AS OutstndByIns
FROM (tsd_Claim LEFT JOIN [qs_SQLClaimStatusLastEntryCL] ON (tsd_Claim.clnum = [qs_SQLClaimStatusLastEntryCL].Claim)
AND (tsd_Claim.clpid = [qs_SQLClaimStatusLastEntryCL].Pat))
LEFT JOIN tsd_Patient ON tsd_Claim.clpid = tsd_Patient.PID
WHERE (((tsd_Patient.PAGENT)=''' + ltrim(rtrim(@strAgent)) + ''')
AND (([qs_SQLClaimStatusLastEntryCL].Pat) Is Null)
AND ((tsd_Claim.clfromdos)<GetDate()- ' + cast(@mea as varchar(12)) + '))
GROUP BY tsd_Claim.clinsnum
ORDER BY Sum(tsd_Claim.cloutstandingamt) DESC'
EXECUTE(@sql)

SET @SQL = 'Create view qs_CallList2 as
SELECT TOP 100 percent tsd_Claim.clinsnum AS [Ins#],
tsd_Claim.clpid AS [Account#],
Sum(tsd_Claim.cloutstandingamt) AS OutstndByAcct
FROM (tsd_Claim
LEFT JOIN [qs_SQLClaimStatusLastEntryCL] ON (tsd_Claim.clnum = [qs_SQLClaimStatusLastEntryCL].Claim)
AND (tsd_Claim.clpid = [qs_SQLClaimStatusLastEntryCL].Pat))
LEFT JOIN tsd_Patient ON tsd_Claim.clpid = tsd_Patient.PID
WHERE (((tsd_Patient.PAGENT)=''' + ltrim(rtrim(@strAgent)) + ''')
AND (([qs_SQLClaimStatusLastEntryCL].Pat) Is Null)
AND ((tsd_Claim.clfromdos)<GETDATE()-' + cast(@mea as varchar(12)) + '))
GROUP BY tsd_Claim.clinsnum, tsd_Claim.clpid
ORDER BY Sum(tsd_Claim.cloutstandingamt) DESC'
EXECUTE(@SQL)

SET @SQL = 'UPDATE tsd_Patient SET tsd_Patient.PPriority = Null WHERE (((tsd_Patient.PAGENT)=''' + ltrim(rtrim(@strAgent)) + '''))'
EXECUTE(@SQL)

set @SQL = 'truncate table priority'
EXECUTE(@SQL)

set @SQL = 'INSERT INTO Priority ( Agent, [Account#] )
SELECT tsd_Patient.PAGENT, tsd_Claim.clpid AS [Account#]
FROM ((tsd_Claim LEFT JOIN tsd_Patient ON tsd_Claim.clpid = tsd_Patient.PID)
INNER JOIN [qs_CallList1] ON tsd_Claim.clinsnum = [qs_CallList1].[Ins#])
INNER JOIN [qs_CallList2] ON (tsd_Claim.clpid = [qs_CallList2].[Account#])
AND (tsd_Claim.clinsnum = [qs_CallList2].[Ins#])
WHERE (((tsd_Claim.cloutstandingamt)>0.99))
GROUP BY tsd_Patient.PAGENT, tsd_Claim.clpid, [qs_CallList1].[Ins#],
[qs_CallList1].OutstndByIns, [qs_CallList2].OutstndByAcct
ORDER BY tsd_Patient.PAGENT, [qs_CallList1].OutstndByIns DESC , [qs_CallList2].OutstndByAcct DESC'
EXECUTE(@SQL)

set @SQL = 'Create view qs_CallList3 as
SELECT TOP 100 Percent tsd_Patient.PAGENT, tsd_Claim.clpid AS [Account#], tsd_Claim.clticketnum AS Claim,
tsd_Claim.clfromdos AS DOS, tsd_Claim.cloutstandingamt AS Outstnd, tsd_Claim.clins AS Insurance,
tsd_Claim.clinsph AS Phone, tsd_Patient.PGLNAME, tsd_Patient.PFNAME AS [First], tsd_Patient.PLNAME AS [Last],
tsd_Patient.PSSN, tsd_Patient.PDOB, [qs_CallList1].OutstndByIns, [qs_CallList2].OutstndByAcct,
[qs_SQLClaimStatusLastEntryCL].Pat

FROM (((tsd_Claim LEFT JOIN tsd_Patient ON tsd_Claim.clpid = tsd_Patient.PID)

INNER JOIN [qs_CallList1] ON tsd_Claim.clinsnum = [qs_CallList1].[Ins#])
INNER JOIN [qs_CallList2] ON (tsd_Claim.clpid = [qs_CallList2].[Account#]) AND (tsd_Claim.clinsnum = [qs_CallList2].[Ins#]))
LEFT JOIN [qs_SQLClaimStatusLastEntryCL] ON (tsd_Claim.clnum = [qs_SQLClaimStatusLastEntryCL].Claim)
AND (tsd_Claim.clpid = [qs_SQLClaimStatusLastEntryCL].Pat)

WHERE (((tsd_Claim.clfromdos)<GetDate()-' + cast(@mea as varchar(12)) + ')
AND ((tsd_Claim.cloutstandingamt)>0.99)
AND (([qs_SQLClaimStatusLastEntryCL].Pat) Is Null
Or ([qs_SQLClaimStatusLastEntryCL].Pat)=''0''))

ORDER BY tsd_Patient.PAGENT, [qs_CallList1].OutstndByIns DESC ,
[qs_CallList2].OutstndByAcct DESC ,
tsd_Claim.clinsnum DESC,
tsd_Claim.clpid,
tsd_Claim.cloutstandingamt DESC'
EXECUTE(@SQL)

set @SQL = 'update tsd_patient set tsd_patient.ppriority = priority.priority from priority where tsd_patient.pid= priority.[account#]'
EXECUTE(@SQL)

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-04-03 : 11:13:48

Give them execute permission on specific SP.

smorty44
Yak Posting Veteran

93 Posts

Posted - 2008-04-03 : 11:35:27

Yes, but that does not allow the sp to complete. I'm getting permission denied and I believe because this sp is dropping views, creating views, updating a table, truncating, and inserting into a table. I've tried giving rights to each table, view, etc. but the only thing that works is giving them db_owner access but I don't want to do that. Is there a better way?

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-04-03 : 11:47:02

No,never give DB_owner permission. They can do anything with database.There are lots of stuff going on inside SP. Better to run as a DBA.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources