Create user with the following permissions

  • Hi,

    I need to create a user with the following permissions.

    Create/Alter/Drop Procedure

    The user should not be able to Create/Alter/Drop Tables.

    Thanx in advance !

    Sanz
  • check this

    grant user on sp

    ________________________________________
    M.I.
    [font="Times New Roman"]

    Learning is a path with no destination...
    [/font]

  • The user should have Create/Alter/Drop permissions for all the Sp's in the database but should not have Create/Alter/Drop Table permissions in the database.

    Sanz
  • I know you can grant CREATE PROCEDURE permission to a user or role, which would allow it to create stored procedures, but you have to grant ALTER permission individually to each procedure after it's created. I think you can grant ALTER permission to the schema to which procedures belong to allow dropping procedures.

    Greg

  • Most of the objects in this database are under the default schema (dbo).

    So giving permissions on the schema won't work.

    Sanz
  • I believe you will need to provide

    ALTER ANY SCHEMA

    CREATE PROCEDURE

    EXECUTE

    privileges on the database.

  • [font="Times New Roman"]If we provide

    grant alter on schema ::dbo TO user1

    then user1 will also be able to drop the tables.

    ... So then you DENY CONTROL on OBJECT::Table1 to user1 ... User1 is now prevented from dropping Table1

    ... But User1 can't even select from Table1 anymore. Since DENY is higher precedence than GRANT, you are unable to even GRANT SELECT on object::Table1 to User1[/font]

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply