1

I have created a stored proc on schema X that does a select across 10+ tables that are in schema X and Y.

I created a database role DBRole and added a new AD Group Login to it.

I thought all I needed to do was grant execute on x.MyStoredProc to DBRole, but I'm getting errors because of select permission..

Stored Procedure MYSCHEMA.MyStoredProc failed: The SELECT permission was denied on the object 'myTable', database 'Db', schema 'dbo'.

I wondered if it was because the tables its failing on are in a different schema but, doing a quick test that still worked..

Can anyone explain what I'm missing?

Brian Tompsett - 汤莱恩
  • 5,753
  • 72
  • 57
  • 129
Staggerlee011
  • 847
  • 2
  • 13
  • 23
  • Adding GRANT SELECT ON dbo.XXX to DBRole has got the stored procedure running, im just wondering why it was needed.. (Is there a diff between 2008r2 and 2014 with calling different schema objects? – Staggerlee011 Nov 19 '15 at 09:29
  • IF owner of the table is same then you don't need to explicitly grant permission for the objects inside the stored procedure. If you are accessing objects of other owners you need permission on the same. – knkarthick24 Nov 19 '15 at 09:34
  • knkarthick24 interesting! Whats best practise for ownership of objects? should it always be dbo even for other schema objects? at present there are 3-4 different schemas all owned by diff users.. – Staggerlee011 Nov 19 '15 at 10:31

0 Answers0