More on user-schema separation. In SQL 2000 and previous versions, granting someone CREATE TABLE privilege meant that they could create tables (no surprise there). The tables were "named after them" (e.g. bob.sometable) unless they were DBO. Because of user-schema separation in SQL Server 2005, that's no longer accurate.
I always explain this in terms of what you may not have the ability to do. And that, unless you have a default schema, your default schema is DBO, which you probably can't write to. There's a more polite/positive way of expressing this, courtesy of Dan. To be able to say…create tables, you need:
1. CREATE TABLE permission
2. A schema in which you are allowed to create objects. Or a schema that you or one of your roles own.
BTW, if this doesn't jibe with your experience, you're probably using the GUI (SSMS) or the legacy system stored proc sp_adduser, which creates a schema for you (for backward compatibility) rather than the new, cool (is security cool? yes, I think so), DDL statement CREATE USER. If you're using SSMS, to see what I mean:
1. Go to Security/logins in Object Explorer.
2. Choose to create a new login. Type in a login name/password
3. Click on database access
4. Permit access to a database by checking the Permit checkbox
5. Note that "default schema" and "user" get filled in with your userid
6. Click in the "default schema" cell. Oh.
NOTE that *default schema is a dropdown list*. You get to choose your default schema, or choose not to have one at all, in which case it's DBO.
One thought on “I have CREATE TABLE privilege but can’t create tables”
To create a table in SQL 2005, at a minimum you need the CREATE TABLE permission and ALTER permission on the target schema. E.g.
GRANT CREATE TABLE TO some_user;
GRANT ALTER ON SCHEMA :: some_schema TO some_user;
Caveat! When you GRANT some_user ALTER on some_schema, you also give some_user the power to DROP tables out of some_schema. Therefore, in practice you will either own or CONTROL the target schema. E.g.,
GRANT CONTROL on SCHEMA :: some_schema TO some_user;
CREATE SCHEMA some_other_schema AUTHORIZATION some_user;
Comments are closed.