Re: Help pls: Not allow users access to tables, stored procedures

Thanks Dan for the sugguestion.

However, I'm not doing any dynamic SQL, only SELECTS, INSERTS, DELETES,
UPDATES.

I hope this isnt going to be a lost cause?

"Dan Guzman" wrote:

Are using dynamic SQL (EXEC or sp_executesql) inside the stored procedures?
Dynamic SQL effectively breaks the ownership chain.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Ben" <ben_1_ AT hotmail DOT com> wrote in message
news:EB49D85E-4E34-450C-B806-89C23B2E865A@xxxxxxxxxxxxxxxx
Thank you for the response.

I have checked everything, and the stored procedure is owned by 'dbo' and
the tables are also owned by 'dbo'. The 'dbo' login is set to the sa
account, but still we are having trouble achieving this 'chaining' affect.

Thanks for the continued help.
Ben

"David Browne" wrote:


"Ben" <ben_1_ AT hotmail DOT com> wrote in message
news:60FD4599-B496-4C65-A863-8D8ADD8D72E9@xxxxxxxxxxxxxxxx
Hello

I have created a database where all the functionality (add, delete,
select,
update, etc) is performed via stored procedures. The front end
(ASP.NET
application) runs the stored procedures. The problem we have run into
is
that when we set up a role for ALL users to have which ONLY has exec
permissions on the stored procedures, we get errors saying we dont have
the
correct permission for select permission on the tables. We have been
able
to
set this up before on other apps, and for the life of us, cannot figure
it
out for this one. All the db settings look the same but the stored
procedures see to requir the users to also have select permission on
the
tables.



Check out Ownership Chains in BOL.
http://msdn2.microsoft.com/en-us/library/ms188676.aspx

Granting execute on a proc will supress permission checks on objects
owned
by the owner of the procedure.

Probably you do not have intact ownership chains from the procedures to
the
tables.

David





.

Relevant Pages

  • Re: Help pls: Not allow users access to tables, stored procedures
    ... Are using dynamic SQL inside the stored procedures? ... correct permission for select permission on the tables. ... Probably you do not have intact ownership chains from the procedures to ...
    (microsoft.public.sqlserver.security)
  • Re: SQL Injection Prevention
    ... > under the impression that all stored procedures contain dynamic SQL. ... more than 1.5 MLoC of Fortran code + more than 1.2 MLoC of C++ ...
    (microsoft.public.sqlserver.server)
  • Re: SQL Injection Prevention
    ... > under the impression that all stored procedures contain dynamic SQL. ... more than 1.5 MLoC of Fortran code + more than 1.2 MLoC of C++ ...
    (microsoft.public.dotnet.security)
  • Re: Dynamic SQL
    ... Dynamic SQL versus stored procedures is really a minimal discussion. ... Obviously if you CAN use stored procedures, you are going to have a MUCH ... With only minimal modification you could make your middle tier use stored ... > While I am not trying to start another discussion about> business rules and where they ...
    (microsoft.public.sqlserver.programming)
  • Re: Confused about proc vs. dynamic SQL vs LINQ
    ... stored procedures are going to be faster than ... performance from dynamic sql (sql that you generate on the client and ... as well as if the queries are the same and a number ... another execution plan would be faster because statistics changed. ...
    (microsoft.public.dotnet.languages.csharp)