Microsoft SQL Server database Server Management maintenance role

fixed server roles:
Described in the order of the lowest-level roles (bulkadmin) to the highest level of the role (sysadmin):
Bulkadmin: Members of this server role can run BULK INSERT statements. This statement allows data to be imported from a text file into a SQL Server 2008 database and is designed for domain accounts that need to be bulk inserted into the database.
Dbcreator: Members of this server role can create, change, delete, and restore any database. This is not only a role for the Assistant DBA, but also a developer-friendly role.
Diskadmin: This server role is used to manage disk files, such as mirror databases and add backup devices. It is suitable for assistant DBAs.
Processadmin:sql Server 2008 can be multitasking, which means that multiple events can be done by executing multiple processes. For example, SQL Server 2008 can generate a process to write data to the cache while generating another process to read the data from the cache. Members of this role can end (known as delete) processes in SQL Server 2008.
Securityadmin: Members of this server role will manage logins and their properties. They can authorize, deny, and REVOKE server-level permissions. You can also authorize, deny, and revoke database-level permissions. Additionally, they can reset the password for the SQL Server 2008 login.
Serveradmin: Members of this server role can change server-wide configuration options and shut down the server. For example, SQL Server 2008 can use much memory or monitor how much information is sent over the network, or shut down the server, a role that can alleviate some administrative burden on administrators.
Setupadmin: Designed for users who need to manage linked servers and control which stored procedures are started. Members of this role can be added to the setupadmin to add, remove, and configure linked servers and to control the startup process.
Sysadmin: Members of this server role have permission to perform any task in SQL Server 2008.
Public: There are two major features, first, no permissions at the initial state, and second, all database users are members of it.

fixed database roles:
Microsoft provides 9 built-in roles to grant users a special set of permissions at the database level.
DB_Owner: Users of this role can perform any action in the database.
Db_accessadmin: Members of this role can add or remove users from the database.
Db_backupopperator: Members of this role allow the database to be backed up.
Db_datareader: Members of this role allow any data to be read from any table.
Db_datawriter: Members of this role allow data to be written to any table.
Db_ddladmin: Members of this role allow any object to be added, modified, or deleted in the database (that is, any DDL statements can be executed).
Db_denydatareader: Members of this role are denied access to any data in the database, but they can still be viewed through stored procedures.
Db_denydatawriter: Like the db_denydatareader role, members of the role are denied the ability to modify any data in the database, but they can still be modified through stored procedures.
Db_securityadmin: Members of this role can change permissions and roles in the database.
Public: Each database user in SQL Server 2008 belongs to the public database role. When a user has not been granted or denied specific permissions to a securable object, this user will be allegedly granted the permissions of the public role of the securable object, and the database role cannot be deleted.

Common permissions:

Security Object	Common permissions
Database	Create DATABASE, create DEFAULT, create FUNCTION, create PROCEDURE, create VIEW, create TABLE, create RULE, BACKUP DATABASE, BACKUP LOG
Table	SELECT, DELETE, INSERT, UPDATE, REFERENCES
Table-Valued functions	SELECT, DELETE, INSERT, UPDATE, REFERENCES
View	SELECT, DELETE, INSERT, UPDATE, REFERENCES
Stored Procedures	EXECUTE, synonym
Scalar functions	EXECUTE, REFERENCES

Operation Permissions:

Permissions are divided into 3 states: Grant, DENY, REVOKE, and the state of the permission can be modified using the following statement.

Grant permission: Grant permission to perform the related action. By role, all members of the role inherit this permission. Revoke permission (REVOKE): revokes a granted permission, but does not display a blocking user or role from performing an action. The user or role can still inherit grant permissions from other roles. Deny permission: Explicitly denies permission to perform an action and prevents the user or role from inheriting permissions, which takes precedence over other granted permissions.

1. Granting Permissions

This syntax format:

GRANT

{all|statement[,.. N]}

To security_account[,.. N

All: Indicates that you want to grant all available permissions to objects of that type. This option is not recommended and some options are reserved for backward compatibility only. Granting the all parameter is equivalent to granting the following permissions: If the securable object is a database, all means create databases, create DEFAULT, create FUNCTION, create PROCEDURE, create VIEW, Create TABLE, create rule, and so on. If the securable object is a scalar function, all represents execute and references. If the securable object is a table-valued function, all represents select, DELETE, INSERT, UPDATE, REFERENCES. If the security object is a stored procedure, all represents execute, synonym. If the securable object is a table, all represents select, DELETE, INSERT, UPDATE, REFERENCES. If the securable object is a view, all represents Select, DELETE, INSERT, UPDATE, REFERENCES. Statement: Represents a command that can be granted permissions, for example, CREATE DATABASE. Security_account: Represents the user unit that defines the permissions that are granted. Security_account can be a database user for SQL Server, a role for SQL Server, or a user or workgroup for Windows