Re: SP Permission Inheritance and Table Creation

From: Meir Simcha Kogan (mkogan_at_chabadonline.com)
Date: 07/27/03 

Date: Sun, 27 Jul 2003 02:42:29 -0400

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
> >
> >
>
>

Relevant Pages

  • Re: public role question
    ... Who is the owner of the database? ... "Dan Guzman" wrote: ... Windows group and thereby get sysadmin permissions. ... Then I am able to create a stored procedure. ...
    (microsoft.public.sqlserver.security)
  • Re: public role question
    ... I logged on to the database through QA as dantest. ... "Dan Guzman" wrote: ... Windows group and thereby get sysadmin permissions. ... Then I am able to create a stored procedure. ...
    (microsoft.public.sqlserver.security)
  • Re: Select Permission Denied On Object
    ... The main consideration with cross database chaining is that the security implications aren't obvious. ... On the other hand, if data are not sensitive and direct selects are no big deal, go with select permissions. ... > I have a stored procedure in one database, ...
    (microsoft.public.sqlserver.security)
  • Re: Security - Stored Procedures
    ... > This is known as ownership chaining. ... permissions are only checked on directly referenced objects. ... You can change database ownership using ...
    (microsoft.public.sqlserver.security)
  • Re: List Users Permissions down to table.column action
    ... THIS STORED PROCEDURE GENERATES COMMANDS ... -- FIXED PROBLEMS WITH STATEMENT LEVEL PERMISSIONS GRANTING. ... -- CREATE TABLE TO HOLD LIST OF USERS IN CURRENT DATABASE ... -- GRANT USER ACCESS TO SERVER ROLES ...
    (microsoft.public.sqlserver.security)