locked
User can delete table but not create one RRS feed

  • Question

  • Hi,

    I've a database user who is unable to create tables or sprocs in a database but can delete them.

    They have control permissions on all the schemas, but have not been granted  create table or create procedure permissions in the database.

    The net effect of this seems to be they can delete objects but not create them.

    It seems a little bizarre, that they get the abilty to delete tables by default, is there a particualr reason for this  ?

     

     

     


    Sean
    Tuesday, December 7, 2010 8:10 AM

Answers

  • Hi Sean,

    The reason why the schema owner can delete objects but be not able to create objects within the schema, that is because the ownership chaining since the schema and objects within the schema are owned by the same user so that SQL Server does not check the access permission. However, to create objects within the schema, the user must have the corresponding CREATE permission.

    For more information, you can refer to CREATE SCHEMA (Transact-SQL) (http://msdn.microsoft.com/en-us/library/ms189462.aspx) and GRANT Schema Permissions (Transact-SQL) (http://msdn.microsoft.com/en-us/library/ms187940.aspx).

    Thanks,
    Chunsong


    Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.
    Thursday, December 9, 2010 6:25 AM

All replies

  • Sean

    USE SecDemo
    GO
    -- Create a dbo table
    CREATE TABLE dbo.t (id int)
    GO
    -- new user
    CREATE LOGIN JoeStrummer WITH password='jj'
    CREATE USER Joe FOR LOGIN JoeStrummer
    GO
    -- this fails, because Joe does not have permissions on schema 'dbo'
    EXECUTE AS USER= 'Joe'
    GO
    CREATE TABLE t1 (id int)
    GO
    REVERT
    GO

    EXECUTE AS USER= 'Joe'
    GO
    DROP TABLE t1--also failed
    GO
    REVERT
    GO


    CREATE SCHEMA JoeSchema AUTHORIZATION Joe
    GO

    EXECUTE AS USER= 'Joe'
    GO
    CREATE TABLE JoeSchema.t1 (id int)
    --CREATE TABLE permission denied in database 'SecDemo'. ---need to grant create table perm
    GO
    REVERT
    GO
    GRANT CREATE TABLE TO Joe

    EXECUTE AS USER= 'Joe'
    GO
    DROP TABLE JoeSchema.t1
    GO
    REVERT
    GO


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    • Proposed as answer by Willy Taveras Tuesday, December 7, 2010 1:59 PM
    Tuesday, December 7, 2010 8:36 AM
  • Hi Sean,

    The reason why the schema owner can delete objects but be not able to create objects within the schema, that is because the ownership chaining since the schema and objects within the schema are owned by the same user so that SQL Server does not check the access permission. However, to create objects within the schema, the user must have the corresponding CREATE permission.

    For more information, you can refer to CREATE SCHEMA (Transact-SQL) (http://msdn.microsoft.com/en-us/library/ms189462.aspx) and GRANT Schema Permissions (Transact-SQL) (http://msdn.microsoft.com/en-us/library/ms187940.aspx).

    Thanks,
    Chunsong


    Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.
    Thursday, December 9, 2010 6:25 AM
  • Chunsong

    Yep it does make sense. Thanks


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Thursday, December 9, 2010 7:54 AM
  • Chunsong

    Yep it does make sense. Thanks


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Thursday, December 9, 2010 7:54 AM