OK - This must be simple - but I am confused. I have Database "GlobalInfo" with a table taPerson - owned by giOwner. I have set u a role called GIAdmin - which has a member called perAdmin.
In the database I have a stored procedure paPersonAdd to insert new entries. Rights are granted as...GRANT EXECUTE ON paPersonAdd TO GIAdmin.
When I log into the system as perAdmin, there are no errors executing paPersonAdd. So far, so good.
I have a sibling database 'LocalInfo' also owned by giOwner. It also has a role of GIAdmin with a member perAdmin. In this DB I have a proc called paLogin with rights granted to GIAdmin. Inside this routine, I execute the statement "SELECT pwd FROM GlobalInfo.dbo.taPerson WHERE LoginCode = @Login".
When I attempt to run the procedure as perAdmin, I get a SELECT permission denied on object 'taPerson'. I thought granting rights to the procedure was enough.
Please set me straight.