Why can't the schema owner create a new table?

Question

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?

Sorry, but what Database Engine are you using? – Zoredache Jul 22 '13 at 22:50 — Zoredache, Jul 22 '13 at 22:50

score 2 · Accepted Answer · answered Jul 23 '13 at 00:02

2

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_ddladmin fixed database role. This role is for backwards compatibility - do not use it for new security implementations.

answered Jul 23 '13 at 00:02

Greenstone Walker

4,319
1
16
23

Thanks! I still don't know why I got that error message, but since it's working now, I will live to troubleshoot that another day! – MLF Jul 24 '13 at 16:18

Why can't the schema owner create a new table?

1 Answers1