To easily manage the permissions in your databases, SQL Server provides several roles that are security principals that group other principals. They are like groups in the Microsoft Windows operating system. Database-level roles are database-wide in their permissions scope.
Creating a Server Role Using Management Studio
Step 1:
In the Object Explorer, choose a SQL Server instance, find the Security folder and expand it. Right-click Server Roles > New Server Role.
Step 2:
In the New Server Role screen, go to the General page. Next, find the -server_role_name dialog box and type a name for the role.
Step 3:
Under Owner, select a server principal to attach to the new role.
Step 4:
Under Securables, you will find a list of server-level securables. Select one or more; you can grant or deny permissions to each securable for your server role.
Step 5:
In the Permissions: Explicit box, choose the check box to “grant, grant with grant, or deny permission” to this server role for the selected securables. In some cases it will not be possible to set permission for all selected securables, and you will see a partial selection.
Step 6:
In the Members page, click Add to add logins (groups or individuals) to your new server role.
Step 7:
In the Memberships page, you can also select the appropriate checkbox to add a user-defined server role as a member of another server role.
Click OK, and you have successfully defined a new server role for your SQL Server instance.
SQL Server provides the following fixed server roles, starting with least privileged roles:
- public—default role for server principals who do not have specific securable object permissions. Only assign public permissions to objects that can be made available to all users. You cannot revoke public permission from any server role.
- dbcreator—can alter, create, drop, or restore databases.
- diskadmin—can manage disk files.
- bulkadmin—can execute BULK INSERT
- setupadmin—can add/remove linked servers and run Transact-SQL
- processadmin—can end running processes in the SQL server instance.
- securityadmin—can administer logins, can reset SQL server login passwords, and grant, deny or revoke server-level permissions or database-level permissions
- serveradmin—can alter server configuration and shut it down
- sysadmin—can perform all server activities.
Microsoft SQL Server provides the following fixed database roles:
db_owner—allowed to perform all maintenance and configuration activities on the database, as well as dropping the database
db_securityadmin—can modify custom role memberships and manage permissions. Monitor this role closely as it has the ability to escalate privileges.
db_accessadmin—can add/remove database access for Windows groups and logins, as well as SQL Server logins
db_backupoperator—can perform database backups
db_ddladmin—can run data definition language (DDL) commands
db_datawriter—can add, change, or delete any user table data.
db_datareader—limited to reading data from user tables
db_denydatawriter—are not allowed to add, modify or delete user table data
db_denydatareader—cannot read any of the data in a user table
