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

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

  • help! report users permission on a SQL 2000 server
    ... stored procedures on each database. ... Is it possible to find permission using ... I read the online book of SQL server for 3 days. ...
    (microsoft.public.sqlserver.security)
  • Re: Permission settings of Inventory database
    ... assign a password and give the required permission to the application role. ... Assign this application role to the database user ... > I'm using a single database for inventory system. ... > database for only inside stored procedures calls and keep restricting the ...
    (microsoft.public.sqlserver.security)
  • Re: Database Model - Class, objects and interaction
    ... I was just proving stored procedures can call views. ... stuff like security and logging. ... So given 100,000 users, you would create database accounts for each? ... Part of our system's integrity was its reliability, and reliability is often assisted by simplicity. ...
    (comp.object)
  • Re: Database Model - Class, objects and interaction
    ... Stored procedures should only be used as a last resort if you cant use ... I've not said that views shouldn't be created, only that they shouldn't be exposed as the interface to the database. ... But business rules can be more complicated than simply defining overdue invoices. ...
    (comp.object)
  • Re: Infinite Loops and Explicit Exits
    ... the whole payroll system would be dependent on proper database ... I design large systems the same way. ... Stored procedures ARE application code. ... any language, including Cobol. ...
    (comp.lang.cobol)