CREATE and INSERT permissions to User_X on DB_Z without DROP or DELETE

• VoldemarG

  Hall of Fame

  Points: 3883

  More actions

  September 2, 2021 at 6:21 pm

  #3926376

  [grant CREATE and INSERT permissions to User_X on all tables in DB_Z

  BUT

  Avoid granting DROP or DELETE permissions].

  Encountered this unusual task today. Other than DDL trigger, what else?...

  if DDL trigger is the only solution, what will it look like exactly?

  Likes to play Chess

• ScottPletcher

  SSC Guru

  Points: 100948

  More actions

  September 2, 2021 at 7:28 pm

  #3926388

  For DELETE, a DENY DELETE should work.

  For DROP, if the user created the table, they likely own it, so you'd have to use a DDL trigger for that AFAIK.

  SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

• September 9, 2021 at 3:01 am

  This was removed by the editor as SPAM

• September 9, 2021 at 3:01 am

  This was removed by the editor as SPAM