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

It looks like I may have been wrong with a previous statement. Not all
tables are owned by dbo. Some are owned by another user. Wouldnt a dbo have
privilege on another user's tables?

If not, it there a way to force this to work WITHOUT giving select
permissions on the tables not owned by dbo?

THanks.

"Dan Guzman" wrote:

Are all the objects in the same database? If not, cross-database chaining
('db chaining' database option) also needs to be enabled. That's the only
other cause of a broken ownership chain not already discussed in this
thread.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Ben" <ben_1_ AT hotmail DOT com> wrote in message
news:86145D51-918F-4255-A875-F3AE8B544CB0@xxxxxxxxxxxxxxxx
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: MS Access cant find Recordsource object
    ... object names and stored procedures that reference objects. ... I guess I'll get busy prepending dbo. ... > since later versions are better able to resolve queries where objects ... If I give those users sysadmin privileges, ...
    (microsoft.public.sqlserver.server)
  • Re: dbo. in vs generated code for stored procedures.
    ... all of your stored procedures be owned by dbo so that you don't have ... stored procedures to be owned by dbo, ... If she is not a sysadmin, ... A developer who is a db_owner can ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Preventing Injection - Client Side
    ... > In addition the program needs to read from ... Are they owned by "dbo"? ... To perform DML operations on tables, I would use stored procedures. ...
    (microsoft.public.sqlserver.security)
  • Re: Help pls: Not allow users access to tables, stored procedures
    ... "Dan Guzman" wrote: ... Dynamic SQL effectively breaks the ownership chain. ... etc) is performed via stored procedures. ... correct permission for select permission on the tables. ...
    (microsoft.public.sqlserver.security)
  • Re: system tables security
    ... EM and look at the Database User Properties Permission for ... permissions for this user' the Owner dbo has Select, ... Insert, Update, Delete and DRI permission,, ... I checked the Server Properties Under Server behavior, ...
    (microsoft.public.sqlserver.security)