hi, i need a userdefined role, if i assign that role to any login he can do any operation on any database EXCEPT delete.
hi i have a requirement as i need to create a server as, if i assign that role to any one he/she can perform any type of operation on any database EXCEPT DELETE. how can create that role any suggestion. Thanks, Ram
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;
Take a look at CREATE ROLE Then take a look at GRANT/REVOKE/DENY Then you want to grant SELECT, INSERT, UPDATE to the role, then DENY DELETE to the role Then assign users to the role who you want this permission to be given too