Re: Help understanding Stored proc Level Secuirty?

From: JWR (jwrugo_at_patmedia.net)
Date: 07/10/03

  • Next message: Tim Munn: "Migrating User from One Domain To Another" 
    Date: Thu, 10 Jul 2003 16:18:39 -0400

    Yes and Yes

    "Jasper Smith" <jasper_smith9@hotmail.com> wrote in message
    news:%23Jrtz9xRDHA.632@TK2MSFTNGP12.phx.gbl...
    > 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.
    > > >
    > > >
    > >
    > >
    >
    >
    >

  • Next message: Tim Munn: "Migrating User from One Domain To Another"

    Relevant Pages

    • Re: Help understanding Stored proc Level Secuirty?
      ... Jasper Smith (SQL Server MVP) ... I set permissions to my Stored Procedures, ... Access to stored procs fail ...
      (microsoft.public.sqlserver.security)
    • Re: Newbie on permissions: ADO.NET, C++.NET, SQL SERVER 2005 EXPRESS, Visual Studio 2005
      ... access for the Visual Studio 2005, when working on ADO.NET (SQL Server ... Permissions are per logins and users. ... permissions can be granted through stored procedures, ...
      (comp.databases.ms-sqlserver)
    • Re: basic question
      ... Currently we have are doing calculations via stored procedures ... in which case *I* would like to have them run on the SQL server ... procs are doing. ...
      (microsoft.public.dotnet.general)
    • Re: Frage zu View-Tabellen
      ... eigenes Element in SQL. ... Erweiterungen sind indizierte Sichten (SQL Server 2000), ... Performance und vom Speicherverbrauch ein Unterschied, ob ich eine Anfrage auf eine View-Tabelle oder eine Stored Procedure schicke? ... Bei Stored Procedures wird der kompilierte Ausführungsplan im Cache gespeichert, der bei Zugriffen auf die View in der Regel neu erstellt wird. ...
      (microsoft.public.de.sqlserver)
    • How do I do Paging through a large dataset via Stored Procedures
      ... Paging by dynamically altering the SQL Query ... Create stored procedures ... SELECT * FROM STUDENTS ...
      (microsoft.public.dotnet.framework.adonet)