Sometimes we may require custom permissions on multiple databases, then we will go for roles. But, not groups. Groups are specific to single database. Where as roles are server level permissions. To create user defined role, we must run create role command from master database.
Syntax:
create role role_name [with passwd "password"]
Example:
1. Create role with the name rw_role.
use master go create role rw_role go
2. Activate the role. To activate role, login must be sso.
set role rw_role on go
3. Grant permissions to role like select on employee table.
use r2schools go grant select,update on employee to rw_role go
4. Finally assign user-defined role to login.
grant role rw_role to test go
5. To check permissions of role
sp_helprotect rw_role go