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?
-
1Sorry, but what Database Engine are you using? – Zoredache Jul 22 '13 at 22:50
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 0:02
-
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
