locked
Sql Server User Permission RRS feed

  • Question

  • Hey ,

    I have a question, i have a database(DB1) and mruser is user who access to db , i want to mruser only have create table and insert data permission. how to set these permission to mruser.


    As Kushwaha

    Monday, April 1, 2013 10:09 AM

Answers

  • The user needs CREATE TABLE permission, which is a database-level permission. The user also needs ALTER permission on the schema is supposed to create tables in. You can grant him ALTER permission on database level, if you want him to create tables in any schema. I recommend that you confine him to any schema.

    INSERT permission can be granted on object, schema and database level. If you want the user to be able to insert data into the newly created table, you will need to grant permission on schema level. Observe that if you create an object in a schema, the owner of the schema also becomes the owner of the table.

    Presumably, the user should also have SELECT permission on the tables, so that he can see what he inserted?

    Granting CREATE TABLE permission:

    GRANT CREATE TABLE TO mruser

    Granting ALTER permission on a schema:

    GRANT ALTER ON SCHEMA::someschema TO mruser

    Granting INSERT permissions is left as a exercise to the reader.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Proposed as answer by Fanny Liu Tuesday, April 2, 2013 7:24 AM
    • Marked as answer by Fanny Liu Friday, April 12, 2013 5:32 AM
    Monday, April 1, 2013 10:23 AM
  • You create a schema with the CREATE SCHEMA command:

    CREATE SCHEMA newschema

    The command must be alone in a batch.

    In all databases there is always the dbo schema, and if you unaware of schemas and don't "use" them, all your objects are in this schema.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Proposed as answer by Fanny Liu Tuesday, April 2, 2013 7:29 AM
    • Marked as answer by Fanny Liu Friday, April 12, 2013 5:32 AM
    Monday, April 1, 2013 11:56 AM
  • Hope this helps!!http://blog.sqlauthority.com/2009/09/07/sql-server-importance-of-database-schemas-in-sql-server/

    Regards http:\\sqldbatask.blogspot.com MCTS Please mark answered if I've answered your question and vote for it as helpful to help other user's find a solution quicker

    • Proposed as answer by Fanny Liu Monday, April 8, 2013 2:25 AM
    • Marked as answer by Fanny Liu Friday, April 12, 2013 5:32 AM
    Tuesday, April 2, 2013 8:31 AM

All replies

  • The user needs CREATE TABLE permission, which is a database-level permission. The user also needs ALTER permission on the schema is supposed to create tables in. You can grant him ALTER permission on database level, if you want him to create tables in any schema. I recommend that you confine him to any schema.

    INSERT permission can be granted on object, schema and database level. If you want the user to be able to insert data into the newly created table, you will need to grant permission on schema level. Observe that if you create an object in a schema, the owner of the schema also becomes the owner of the table.

    Presumably, the user should also have SELECT permission on the tables, so that he can see what he inserted?

    Granting CREATE TABLE permission:

    GRANT CREATE TABLE TO mruser

    Granting ALTER permission on a schema:

    GRANT ALTER ON SCHEMA::someschema TO mruser

    Granting INSERT permissions is left as a exercise to the reader.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Proposed as answer by Fanny Liu Tuesday, April 2, 2013 7:24 AM
    • Marked as answer by Fanny Liu Friday, April 12, 2013 5:32 AM
    Monday, April 1, 2013 10:23 AM
  • The user needs CREATE TABLE permission, which is a database-level permission. The user also needs ALTER permission on the schema is supposed to create tables in. You can grant him ALTER permission on database level, if you want him to create tables in any schema. I recommend that you confine him to any schema.

    INSERT permission can be granted on object, schema and database level. If you want the user to be able to insert data into the newly created table, you will need to grant permission on schema level. Observe that if you create an object in a schema, the owner of the schema also becomes the owner of the table.

    Presumably, the user should also have SELECT permission on the tables, so that he can see what he inserted?

    Granting CREATE TABLE permission:

    GRANT CREATE TABLE TO mruser

    Granting ALTER permission on a schema:

    GRANT ALTER ON SCHEMA::someschema TO mruser

    Granting INSERT permissions is left as a exercise to the reader.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    sir you can define more about schema.and how can we create schema.

    As Kushwaha

    Monday, April 1, 2013 10:53 AM
  • You create a schema with the CREATE SCHEMA command:

    CREATE SCHEMA newschema

    The command must be alone in a batch.

    In all databases there is always the dbo schema, and if you unaware of schemas and don't "use" them, all your objects are in this schema.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Proposed as answer by Fanny Liu Tuesday, April 2, 2013 7:29 AM
    • Marked as answer by Fanny Liu Friday, April 12, 2013 5:32 AM
    Monday, April 1, 2013 11:56 AM
  • i am getting some confusion please provide me information in brief .

    i am waiting your answer. 

    Thank you.


    As Kushwaha

    Tuesday, April 2, 2013 8:02 AM
  • Hope this helps!!http://blog.sqlauthority.com/2009/09/07/sql-server-importance-of-database-schemas-in-sql-server/

    Regards http:\\sqldbatask.blogspot.com MCTS Please mark answered if I've answered your question and vote for it as helpful to help other user's find a solution quicker

    • Proposed as answer by Fanny Liu Monday, April 8, 2013 2:25 AM
    • Marked as answer by Fanny Liu Friday, April 12, 2013 5:32 AM
    Tuesday, April 2, 2013 8:31 AM