Permissions on stored procedures & tables

From: Daniel Wilson (d.wilson_at_embtrak.com)
Date: 02/24/05 

Date: Wed, 23 Feb 2005 18:53:53 -0500

At one client site, the DB server has 2 databases, that of my application
and that for another application. The client's consultant has added a
stored procedure which I am to access.

Signed onto my application's DB, I run the query:
Exec OtherDB.dbo.MyStoredProc Arg1, Arg2, Arg3

I did get an error saying my user wasn't valid on the other DB so I added it
& granted it Execute permissions on MyStoredProc. Now I get error messages
saying:
SELECT permission denied on object 'OTHERTABLE', database 'OtherDB', owner
'OtherUser'

I thought having the right to execute the stored procedure, I shouldn't need
explicit rights to the tables from which it selects.

What do I need to do here? Short of granting myself rights to a bunch of
tables in the other DB?

Thanks. 

-- 
Daniel Wilson
Senior Software Solutions Developer
Embtrak Development Team
http://www.Embtrak.com
DVBrown Company

Relevant Pages

  • Re: Stored procedure
    ... the stored procedure. ... to use in ISAM databases (yes... ... fields are not used in SQL Server except in rare situations. ... > @Param3 money, ...
    (microsoft.public.dotnet.languages.csharp)
  • Covering index causes blocking on exclusive latch acquisition in tempdb?
    ... We have around two dozen databases with the same schema. ... The stored procedure definition is below as well. ... that introducing non-clustered covering indexes on each of these tables ... The queries were even accessing different databases in the blocking chain, ...
    (microsoft.public.sqlserver.programming)
  • Re: Controlling Access to data from multiple databases
    ... this doesn't work since within the body of the stored procedure it reaches ... procedure fails on permissions. ... read only permission to all databases my stored procedure depends on. ... Links for SQL Server Books Online: ...
    (microsoft.public.sqlserver.security)
  • Re: Stored procedure
    ... Thanks Nick, I actually did create them in the sql server, I just didn't how ... > the stored procedure. ... > to use in ISAM databases (yes... ... >> @Param3 money, ...
    (microsoft.public.dotnet.languages.csharp)
  • Re: Data driven people arguments
    ... databases are good at, then even if there's just a little of that, I ... >implemented as a stored procedure; I'm fine with it because up to now ... >Now my colleague says that it's better to put the logic into the sp; ... >able to convince each other. ...
    (comp.object)