Different SQL Server Roles:-
Server Roles:-
The Server Roles page lists all possible roles that can be assigned to the new login. The following options are available:
bulkadmin:-
Members of the bulkadmin fixed server role can run the BULK INSERT statement.
dbcreator:-
Members of the dbcreator fixed server role can create, alter, drop, and restore any database.
diskadmin:-
Members of the diskadmin fixed server role can manage disk files.
processadmin:-
Members of the processadmin fixed server role can terminate processes running in an instance of the Database Engine.
public:-
All SQL Server users, groups, and roles belong to the public fixed server role by default.
securityadmin:-
Members of the securityadmin fixed server role manage logins and their properties. They can GRANT, DENY, and REVOKE server-level permissions. They can also GRANT, DENY, and REVOKE
database-level permissions. Additionally, they can reset passwords for SQL Server logins.
serveradmin:-
Members of the serveradmin fixed server role can change server-wide configuration options and shut down the server.
setupadmin :-
Members of the setupadmin fixed server role can add and remove linked servers, and they can execute some system stored procedures.
sysadmin :-
Members of the sysadmin fixed server role can perform any activity in the Database Engine.
Database-Level Roles:-
db_owner :- Members of the db_owner fixed database role can perform all configuration and maintenance activities on the database,
and can also drop the database.
db_securityadmin:- Members of the db_securityadmin fixed database role can modify role membership and manage permissions. Adding principals
to this role could enable unintended privilege escalation.
db_accessadmin :- Members of the db_accessadmin fixed database role can add or remove access to the database for Windows logins, Windows groups,
and SQL Server logins.
db_backupoperator :- Members of the db_backupoperator fixed database role can back up the database.
db_ddladmin :- Members of the db_ddladmin fixed database role can run any Data Definition Language (DDL) command in a database.
db_datawriter:- Members of the db_datawriter fixed database role can add, delete, or change data in all user tables.
db_datareader :- Members of the db_datareader fixed database role can read all data from all user tables.
db_denydatawriter :- Members of the db_denydatawriter fixed database role cannot add, modify, or delete any data in the user
tables within a database.
db_denydatareader :- Members of the db_denydatareader fixed database role cannot read any data in the user tables within a database.
Server Roles:-
The Server Roles page lists all possible roles that can be assigned to the new login. The following options are available:
bulkadmin:-
Members of the bulkadmin fixed server role can run the BULK INSERT statement.
dbcreator:-
Members of the dbcreator fixed server role can create, alter, drop, and restore any database.
diskadmin:-
Members of the diskadmin fixed server role can manage disk files.
processadmin:-
Members of the processadmin fixed server role can terminate processes running in an instance of the Database Engine.
public:-
All SQL Server users, groups, and roles belong to the public fixed server role by default.
securityadmin:-
Members of the securityadmin fixed server role manage logins and their properties. They can GRANT, DENY, and REVOKE server-level permissions. They can also GRANT, DENY, and REVOKE
database-level permissions. Additionally, they can reset passwords for SQL Server logins.
serveradmin:-
Members of the serveradmin fixed server role can change server-wide configuration options and shut down the server.
setupadmin :-
Members of the setupadmin fixed server role can add and remove linked servers, and they can execute some system stored procedures.
sysadmin :-
Members of the sysadmin fixed server role can perform any activity in the Database Engine.
Database-Level Roles:-
db_owner :- Members of the db_owner fixed database role can perform all configuration and maintenance activities on the database,
and can also drop the database.
db_securityadmin:- Members of the db_securityadmin fixed database role can modify role membership and manage permissions. Adding principals
to this role could enable unintended privilege escalation.
db_accessadmin :- Members of the db_accessadmin fixed database role can add or remove access to the database for Windows logins, Windows groups,
and SQL Server logins.
db_backupoperator :- Members of the db_backupoperator fixed database role can back up the database.
db_ddladmin :- Members of the db_ddladmin fixed database role can run any Data Definition Language (DDL) command in a database.
db_datawriter:- Members of the db_datawriter fixed database role can add, delete, or change data in all user tables.
db_datareader :- Members of the db_datareader fixed database role can read all data from all user tables.
db_denydatawriter :- Members of the db_denydatawriter fixed database role cannot add, modify, or delete any data in the user
tables within a database.
db_denydatareader :- Members of the db_denydatareader fixed database role cannot read any data in the user tables within a database.
