Re: Stored Procedure Security/Permissions

From: Jasper Smith (jasper_smith9_at_hotmail.com)
Date: 09/24/04 

Date: Fri, 24 Sep 2004 19:22:14 +0100

Have a look at

INF: Cross-Database Ownership Chaining Behavior Changes in SQL Server 2000
Service Pack 3
http://support.microsoft.com/?kbid=810474 

-- 
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Tom C" <anonymous@discussions.microsoft.com> wrote in message 
news:324801c4a25e$9ea2a020$a601280a@phx.gbl...
>I have a stored procedure that selects from tables in
> multiple databases on the same server. The stored
> procedure is cataloged in one of the databases.
>
> For example:  The stored procedure  TESTA  is cataloged in
> cede_70  by cede_70_dbo who is the dbo of both databases
>
> create procedure dbo.TESTA
> as
>
> select *
> from  cede_70.dbo.tableA
>   ,  cede_rpt.dbo.tableB
> where cede_70.dbo.tableA.columnone =
> cede_rpt.dbo.tableB.columnone
>
> go
> grant  execute  on  TESTA  to public
> go
>
>
> Under SQL server 2000 SP3a when someone executes the
> stored procedure TESTA it fails with a message like
> "SELECT permission denied on object 'TableB',
> database 'cede_rpt', owner 'dbo'. "
>
> The login executing the stored procedure does not have
> permission to read either table; but the login does have
> permission to execute the stored procedure, which was
> cataloged by the dbo (who has permission to read both
> tables).  It is the read of the table from the 'other'
> database (the one without the stored procedure) which
> fails.
>
> Should I really have to add the User to the
> cede_rpt.dbo.tableB database too! I thought the mere fact
> that it has EXECUTE permission to the SP would suffice!
>

Relevant Pages

  • Re: SQL CLR Sproc isnt running right
    ... did you grant rights using the SQL Server 2005 Surface Area Config tool? ... and I got a permissions error back saying DBO doesn't have permission to insert records into the table... ... but the assemblie's stored procedures do all have execute permissions set for the role that the executing user is in... ... "William Vaughn" wrote in message ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Problem with the Legacy ASP files and the Sql Server Express
    ... It looks like it is not going to be an easy job getting it to work on the SQL Server 2005 and Express:(. ... 'Then you can execute the command and then retrieve the ID ... I am 100% sure this code works against any version of SQL Server, as long as the connection is OK and the SP has two parameters @varCompany as Input and @Return_Value as Output. ... I do not think your problem is due to difference of SQL Server2000 and SQL Server2005, unless your stored procedure has some thing that only works in SQL Server2000, not SQL Server 2005. ...
    (microsoft.public.dotnet.framework.aspnet)
  • Re: Execute stored procedure only
    ... You need CONNECT capability to be able to get into the database and execute ... You can compare this to having permission to eat a piece of pie, ... Should I not be able to just execute SP through QA? ... right to execute stored procedure in database called "mydatabase". ...
    (microsoft.public.sqlserver.security)
  • Re: EXECUTE permission denied on object... uh?
    ... connecting with doesn't have permission to execute the Stored Procedure ... runBehavior, SqlCommand cmdHandler, SqlDataReader ...
    (microsoft.public.dotnet.framework.aspnet)
  • Connection.Execute and SQL stored procedures
    ... ..asp, the connection object and MS SQL Server as the DB can help me out. ... Five of the six calls execute as expected. ... This particular stored procedure does quite a bit of work within the DB, ...
    (microsoft.public.sqlserver.odbc)