I have a DB user called STANMAN which owns the STANMAN schema. When I try to create a table as STANMAN, it says STANMAN has insufficient privileges. How could a schema-owner not already have sufficient privileges? Naturally, when I try to GRANT ALTER or GRANT CREATE, I get "Cannot grant, deny, or revoke permissions to sa, dbo, entity owner, information_schema, sys, or yourself." What am I doing wrong?
Why can’t the schema owner create a new table
schema
Related Question
- When to create a new table if functionality is the same
- Postgresql – Change owner of all schema objects
- Sql-server – What would cause newly restored SQL Databases to show tables, but display an ‘Invalid Object’ error when trying to view table data
- Is it possible grant a PRIVATE Synonym from an the Table Owner schema to an User schema
- How to grant permissions to database but restrict to certain schemas
- Sql-server – the purpose of “Schema Owner”
- Sql-server – Why can’t I create a new schema with the owner set to dbo
- Sql-server – Deny DDL_ADMIN permissions to a role on [dbo] schema
Best Answer
If you are using SQL Server 2005 or later then to create an table in a schema you need both CREATE TABLE at the database level and ALTER at the schema level. Ownership of a schema covers the ALTER permission requirement but not the CREATE TABLE one.
CREATE TABLE is also granted by membership in the
db_ddladminfixed database role. This role is for backwards compatibility - do not use it for new security implementations.