question

There are 3 types of roles in SQL Server: 1. Server Roles: Handle instance-level permissions. Prior to SQL Server 2012, these were only system-defined. 2. Database Roles: Used to assign permissions to database objects. These work in a similar fashion to Windows groups. 3. Application Roles: Also defined at the database level. These are used to grant a user special permission when connecting through an application. You need to set database-level permissions, but you want to do it for all user databases in the instance. I say "user" databases because I'm assuming you don't actually want to change permissions for system databases (in particular, tempdb). You will need to create a database role in each database. If you can clarify "any type of operation ... except delete", I could provide you with a script to do this. For example, if you want to grant just SELECT, INSERT, and UPDATE: CREATE ROLE CannotDelete; GRANT SELECT, INSERT, UPDATE TO CannotDelete; You do not have to explicitly deny delete unless you want to ensure they can never delete even if the user is also a member of another role with delete permission. If you do want to explicitly deny delete, also execute this: DENY DELETE TO CannotDelete;