Re: SP Permission Inheritance and Table Creation

From: Dan Guzman (danguzman_at_nospam-earthlink.net)
Date: 07/27/03

  • Next message: Giacomo: "Re: Unattended.iss" 
    Date: Sun, 27 Jul 2003 10:18:27 -0500

    The same CREATE statement permissions apply regardless of how the CREATE
    statement is executed. You can't restrict a statement permission so
    that is allowed only within a proc.

    An approach you might consider is to employ application roles. This
    allows users to connect with their normal login yet allow the
    application to enable a more powerful security context that can only be
    used within the application.

    Can you elaborate on why users need to execute DDL from within your app?
    This is not a best practice and perhaps someone can suggest a better
    alternative. 

    -- 
Hope this helps.
Dan Guzman
SQL Server MVP
"Meir Simcha Kogan" <mkogan@chabadonline.com> wrote in message
news:OVQK3oAVDHA.2004@TK2MSFTNGP10.phx.gbl...
> So is there anyway way to allow a user to create tables via a SP
without
> jeapordizing permissions on the whole database, i.e. can I restrict
table
> creations outside the SP?
>
> Thanks
>
> -- 
> Meir Simcha Kogan
> Chabad.org Development Team
> "Dan Guzman" <danguzman@nospam-earthlink.net> wrote in message
> news:OKrtJRxUDHA.1916@TK2MSFTNGP12.phx.gbl...
> > Don't confuse statement permissions with object permissions.
Statement
> > permissions, like CREATE TABLE, give the user the right to create
> > objects under their own userid.  A user needs to be a member of a
more
> > powerful role (ddl_admin or db_owner) to create objects owned by
dbo.
> >
> > Ownership chaining applies only to object permissions (SELECT,
INSERT,
> > DELETE, UPDATE, EXECUTE and REFERENCES).  This is not inheritance.
An
> > unbroken township chain simply means that permissions on indirectly
> > referenced objects are not checked.  A user only needs permissions
on
> > the directly referenced objects.  Cross database chaining extends
the
> > chaining concept to include inter-database access.
> >
> > -- 
> > 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
> > -----------------------
> >
> >
> > "Meir Simcha Kogan" <mkogan@chabadonline.com> wrote in message
> > news:ulUV8jtUDHA.2204@TK2MSFTNGP12.phx.gbl...
> > > Hi,
> > >
> > > We have an application that connects to our sql server with a
limited
> > access
> > > account - we control permissions via stored procedures. That is
the
> > only
> > > thing this user has access to and permissions are controlled from
> > within the
> > > stored procedure -- actions on the db are performed using
permissions
> > > inherited from the creator of the stored procedure using cross
> > database
> > > chaining....
> > >
> > > My question involves a stored procedure which is supposed to
create
> > and
> > > update a table. The problem is that it seems that permissions do
not
> > get
> > > inherited from the SP to create new tables...
> > >
> > > How can I allow this SP to create a new table without adding
"Create
> > Table"
> > > permission for this user on the db. In addition, if it could
create
> > it, it
> > > creates the table with this users name and I would like the table
to
> > be
> > > created under the dbo as all our code is already referencing the
> > tables with
> > > dbo.tablename. I tried SP_ChangeObjectOwner - but it seems it does
not
> > have
> > > permissions in the SP to do that - even though the SP creator is a
> > db_owner.
> > >
> > > Thank you for your help.
> > >
> > >
> > > -- 
> > > Meir Kogan
> > >
> > >
> >
> >
>
>
  • Next message: Giacomo: "Re: Unattended.iss"

    Relevant Pages

    • Re: SP Permission Inheritance and Table Creation
      ... table serves as a 'static index' that we generate for quicker access to ... > The same CREATE statement permissions apply regardless of how the CREATE ... >>> referenced objects are not checked. ...
      (microsoft.public.sqlserver.security)
    • Re: "REVOKE ALL FROM public" not working
      ... Statement permissions ... The REVOKE ALL only applies to the statement permissions, ... > INSERT, UPDATE, DELETE on all user tables and EXECUTE on all sprocs. ...
      (microsoft.public.sqlserver.security)
    • Re: Rights to execute jobs?
      ... Does anyone know if the rules surrounding stored procedure permissions apply ... Does this apply to a proc calling sp_start_job so that I ... raise the alert, so it could be anyone on the server. ... is that a group needs to not be an admin but still be able to execute ...
      (microsoft.public.sqlserver.security)
    • Re: Newbie
      ... I will now tell you some basic commands. ... Easy file has rights and a owner. ... file and who may execute it. ... The last three characters are about the permissions of user ...
      (alt.linux)
    • Re: Execute Persmission denied on object sp_OACreate
      ... SQL Server doesn't check permissions on indirectly referenced objects as ... You can prevent ad-hoc execution of powerful master database procs while ... >I have a user who has execute permissions on a store procedure in a>database> which in turns executes 4 stored procedures in the master database. ...
      (microsoft.public.sqlserver.security)