There are two types of roles at SQL Server Instance level.
- Fixed Roles or Predefined roles.
- Customised roles or User defined roles.
The following are various fixed roles.
- bulkadmin –> This role can perform bulk insert operations on database.
- dbcreator –> This role can perform to create/alter/drop/restore a database.
- diskadmin –> Members of this role can manage disk files on server and all DB’s.
- process admin –> Members of this role can manage and terminate/kill processes in SQL Server.
- securityadmin –> Members of this role can manage all logins and resetting the passwords as needed.
- setup admin –> Members of this role can add/remove linked servers.
- sysadmin –>Members of this role can do all actions, it becomes admin rights.
- serveradmin –> Members of this role can change server wide configurations and shutdown the SQL Server.
- public –> This role is by default assigned to every login, it can do basic level activities.
2. We can create our own customized roles.
Syntax for creating custom server role.
create server role <role-name> authorization <server-principal>
create server role myrole authorization bulkadmin;
For example we need to create one custom role and assigned to particular login that custom role contains bulkadmin,dbcreator and processadmin permissions.