Re: Help understanding Stored proc Level Secuirty?

From: Jasper Smith (jasper_smith9_at_hotmail.com)
Date: 07/10/03 

Date: Thu, 10 Jul 2003 21:08:39 +0100

Are the procedures and tables owned by the same user ?
Are you using dynamic sql in your stored procedures ? 

-- 
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"JWR" <jwrugo@patmedia.net> wrote in message
news:OKI7zIxRDHA.2148@TK2MSFTNGP12.phx.gbl...
Thanks very much for your advice; it has been very helpful.
I, however, have a problem.... I set permissions to my Stored Procedures,
without setting any permissions to the tables.  Access to stored procs fail
if the permissions for the logged in user does not extend to the table too.
What am I doing wrong here?
John.
"Dan Guzman" <danguzman@nospam-earthlink.net> wrote in message
news:ud72XauRDHA.2460@TK2MSFTNGP10.phx.gbl...
> If your stored procedure performs data modifications, you shouldn't
> grant EXECUTE to read-only roles.  Permissions on objects accessed by
> the proc are not checked if the ownership chain is unbroken (the objects
> involved have the same owner).
>
> To implement your security model, you can grant SELECT on tables/views
> to your ReadOnly role as well as EXECUTE on read-only procs.  Grant ALL
> on all objects to your FullAcess role.  You might also consider granting
> permissions only on procedures and views, prohibiting direct access to
> tables since this will facilitate implementing row-level security.
>
> See Using Ownership Chains in the SQL Server 2000 Books Online
> <adminsql.chm::/ad_security_4iyb.htm> for more information.
>
> -- 
> Hope this helps.
>
> Dan Guzman
> SQL Server MVP
>
> -----------------------
> SQL FAQ links (courtesy  Neil Pike):
>
> http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
> http://www.sqlserverfaq.com
> http://www.mssqlserver.com/faq
> -----------------------
>
> "JWR" <jwrugo@patmedia.net> wrote in message
> news:eZc%23RwtRDHA.212@TK2MSFTNGP10.phx.gbl...
> > Hi all,
> >
> > I have an VB.NET app that ran and failed authentication a while back.
> I
> > fixed it by setting my Stored Procedure Level Permissions to Role:
> Public
> > and EXEC checked.  This works; but now I want to implement groups and
> role
> > level permissions for General "READ ONLY" and "Full Access" groups.
> >
> > Question is: do I have to do anything different with the Stored Proc
> > security or just associate permissions to the tables?
> >
> > Thanks,
> > John.
> >
> >
>
>

Relevant Pages