Re: Permission problems user can't create a table



Thanks Dan
I have been still thinking in context of SQL Server 2000 ( db_owner role)
instead of GRANT ALTER ON SCHEMA :-)))



"Dan Guzman" <guzmanda@xxxxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:12DA1A44-0382-46EA-81D5-4386E46F7CFD@xxxxxxxxxxxxxxxx
Hi, Uri.

But the user will still specify schema name to create a table. I
understood that the OP wanted to create a table without specifying the
schema, as it must ne a member at least of db_owner fixed database role

The schema is optional. The script below shows that the table is created
in the Sales schema even when no schema is specified. However, the schema
will need to be specified for schema other than the default.

CREATE SCHEMA Sales;

CREATE USER MyUser WITHOUT LOGIN
WITH DEFAULT_SCHEMA = Sales;

GRANT CREATE TABLE TO MyUser;

GRANT ALTER ON SCHEMA::Sales TO MyUser;

EXECUTE AS USER = 'MyUser';

CREATE TABLE MyTable(col1 int);


--
Hope this helps.

Dan Guzman
SQL Server MVP

"Uri Dimant" <urid@xxxxxxxxxxx> wrote in message
news:eqCKcMYuHHA.3476@xxxxxxxxxxxxxxxxxxxxxxx
Hi Dan

But the user will still specify schema name to create a table. I
understood that the OP wanted to create a table without specifying the
schema, as it must ne a member at least of db_owner fixed database role

Just my two cents


"Dan Guzman" <guzmanda@xxxxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:32A22328-F7A8-4961-8605-8E6BD8CDCEE7@xxxxxxxxxxxxxxxx
Thanks again, I guess then that even though a users default schema is
say the dbo schema it doesnt automatically mean they have permissons
on that schema.

Yes - the default schema only specifies the schema used for unqualified
object references. It does not grant permissions.

To create a table, ALTER permission on the schema are needed as well as
CREATE TABLE permissions in the database. For example:

GRANT CREATE TABLE TO MyUser;
GRANT ALTER ON SCHEMA::Sales TO MyUser;
GRANT ALTER ON SCHEMA::Accounting TO MyUser;


--
Hope this helps.

Dan Guzman
SQL Server MVP

<frant101@xxxxxxxxxxxxxx> wrote in message
news:1183029214.769040.183400@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
Thanks again, I guess then that even though a users default schema is
say the dbo schema it doesnt automatically mean they have permissons
on that schema. If for example i wanted to give a user the rights to
create a table in a schema but didnt want to run the Authorization
command (i'm guessing this command makes the user/role owner of that
schema) is there a way

Thanks
Fran








.



Relevant Pages

  • Re: SQL 2005 - Schema permissions
    ... Have you looked at GRANT ALTER? ... the securable (schema or object) but not change the ownership. ... GRANT CONTROL but that's probably too much privilege. ... > how to do it on SQL 2005? ...
    (microsoft.public.sqlserver.security)
  • Re: Permission problems user cant create a table
    ... The schema is optional. ... CREATE USER MyUser WITHOUT LOGIN ... GRANT ALTER ON SCHEMA::Sales TO MyUser; ... I understood that the OP wanted to create a table without specifying the schema, as it must ne a member at least of db_owner fixed database role ...
    (microsoft.public.sqlserver.security)
  • Re: Permission problems user cant create a table
    ... But the user will still specify schema name to create a table. ... say the dbo schema it doesnt automatically mean they have permissons ... GRANT ALTER ON SCHEMA::Accounting TO MyUser; ...
    (microsoft.public.sqlserver.security)
  • ANN: Sequel 2.7.0 Released
    ... Sequel provides thread safety, connection pooling and a concise DSL ... MySQL, ODBC, OpenBase, Oracle, PostgreSQL and SQLite3. ... specifying the name of the column that the:key option ... in many places the schema is ...
    (comp.lang.ruby)
  • Best Practices Analyser
    ... One or more objects are referencing tables/views without specifying a ... improved by specifying schema names. ... CREATE TRIGGER "Aktenplan_DTrig" ON Aktenplan FOR DELETE AS ...
    (microsoft.public.de.sqlserver)