SQL Server Performance Forum – Threads Archive

Her’s a problem

I have two databases DA and DB, I create a stored procedure that selects records from tables on both databases, I save the stored procedure on DA, I give execute permissions to users on procedure. When they run stored procedure, sql server returns an error message that says:
"user x don’t have select permissions on table x", meaning for tables that are on DB, it requires select permissions explicitly. Is there a way to avoid granting users select permissions on tables? Any suggestions would be helpful
thanks

---

What login did you use when created both Procedure and tables? Can you post the sp code?

---

I’m member of system administrator server roles
and also dbo on both databases ==============================
SELECT TaxPeriod,GMSTotalCont,SUM(ISNULL(AMOUNT_PAID,0)) AS AMOUNT_PAID
FROM [KPST_DATA].dbo.tblPNGeneratedMonthlyStatement GM
LEFT JOIN TAQCHECK.dbo.imp_mpd MD
ON GM.GMSSerialNumber = MD.EXT_DOC_NO–LEFT(MD.EXT_DOC_NO,LEN(MD.EXT_DOC_NO)-2)
WHERE GM.FiscalNo = @prmFiscalNo
GROUP BY TaxPeriod,GMSTotalCont
HAVING GMSTotalCont = SUM(ISNULL(AMOUNT_PAID,0))
====================================
databases:
KPST_DATA,TAQCHECK
the procedure is saved in KPST_DATA

---

Take a look at http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=7419

---

]]>