Re: SQL 2000 Sproc Role security bypassed when using QBF?

Alec MacLean (alec.maclean@xxxxxxxxxxxxxxxxxxx) writes:
I should add that the dynamically built Varchar is run using the syntax
"Execute(@theVCstring)".

I am aware I can reduce my admin by adding to the existing role, or
creating an additional new role that manages the direct table select
permissions (I don't need or want to let the app use db_datareader), but
I wonder why the EXECUTE function requires the additional SELECT
permission that sprocs normally inherit?

Because the ownership chain is broken. Access to tables through stored
procedures relies on the fact that the stored procedure and the tables
have the same owner. But a batch of dynamic SQL does not have any owner.

Since you are on SQL 2000, you are stuck. On SQL 2005 there are other means
to grant permissions through stored procedures than ownership chaining.

If you are curious, I have an article on my web site about these method.
This article also discusses ownership chaining in more detail.
http://www.sommarskog.se/grantperm.html.


--
Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
.

Relevant Pages

  • Re: Deny access to all users (including Administrator and DomainAd
    ... permissions on stored procedure override ... applictaion's user account permissions to the stored procedure which will ... Will try to deny access through the query for SQL Server 2005 and 2000. ... Remove everyone that you don't want rom an Administrator Groups ...
    (microsoft.public.sqlserver.security)
  • Re: Set perissions on stored procedure
    ... stored procedure for one of my database roles, ... stored procedure and choosing All Tasks -> Manage Permissions. ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ...
    (microsoft.public.sqlserver.security)
  • Re: Stored procedure loses permissions daily
    ... Columnist, SQL Server Professional ... Senior Database Administrator ... > I have a stored procedure in a SQL 2000 database that loses all of the ... permissions back to what it should be -- very frustrating. ...
    (microsoft.public.sqlserver.security)
  • Re: Chain of ownership
    ... What will you do when John leaves the company?... ... the database which has permissions on the Stored procedure AND permissions ... Wayne Snyder, MCDBA, SQL Server MVP ...
    (microsoft.public.sqlserver.programming)
  • Re: Stored Procedure security
    ... Ownership chaining only applies to object permisions, such as SELECT, ... Instead of TRUNCATE TABLE, you can simply DELETE FROM TABLE and get the same ... I have granted execute permissions ... on the stored procedure to a user x. ...
    (microsoft.public.sqlserver.security)
Quantcast