How to gice drop permission for store procedures

• pols

  SSC Eights!

  Points: 935

  More actions

  January 5, 2011 at 4:42 am

  #231662

  Hi,

  My actual requirement is users can not create /alter the table schema.But they can have insert,select and update data permissions on table. But they can create /alter/drop storeprocedures..

  My default schema for user is dbo. i ahve given below permissions..

  GRANT EXEC ON SCHEMA::dbo TO uname -- Grant perpmission on all procedures in the dbo schema

  DENY CREATE TABLE TO uname

  GRANT ALTER ON SCHEMA::dbo TO uname -- For altering the sps..

  But as per requirement the users can not alter the table schema but they can have permissions for alter and drop the sps.. Pls help ho wto do it..

  Thanks..

• Piotr.Rodak

  SSCrazy Eights

  Points: 9376

  More actions

  January 5, 2011 at 12:23 pm

  #1269699

  Have you considered DDL triggers? You could have a trigger that would log all DDL changes and could rollback any alterations to table schemas.

  Regards

  Piotr

  ...and your only reply is slàinte mhath