SQL Server servers and database roles

First, we explain the server (instance level) permissions, the maximum difference between the instance level and the database-level permissions is that the instance-level permissions are directly granted to the login, and the database-level full display is given to the database user, and the database user then matches the login name. (In SQL Server, logins and users are two concepts, logins are used to log on to the DB instance, and the user is within the database and is used to match the login name)

Example:

--Specify the login name Dbtester and create the user in the test database Tester1
Execute sp_grantdbaccess ' dbtester ', ' tester1 '

Within a server (instance) scope, different permissions can be granted for different objects, mainly in the following ways: Endpoint, login, high availability Group, custom server role, fixed server role. Use sys.server_principals and sys.server_permissions to view the permissions that have been granted.

Example:

It is important to note that control Server grants the user Super Administrator the right to give the user the highest control over SQL Server, so this permission is generally not granted.

Focus on the role of fixed server, altogether nine are:

Sysadmin
Performs any action in SQL Server and is also a permission for any operation on any database.

ServerAdmin
Configure the server settings and use shutdown to stop the instance's permissions.

Setupadmin
Add additional logins to the server role, add, remove, or configure linked servers, and perform some system procedures, such as sp_serveroption.

Securityadmin
Logins for managing logins, reading error logs, and creating database permissions can perform all actions on server access and security.

Processadmin
Manages the SQL Server process and kills other user processes in the instance.

DBCreator
Creating and modifying databases

Diskadmin
Permissions to manage the physical database.
Bulkadmin

Permission to bulk insert data into the database (added in SqlServer2005)

Public

itself does not grant any permissions to other members. Only connection permissions.

fixed database roles are defined on the database tier, so they exist in each database that belongs to the database server. Lists all of the fixed database roles.

db_owner
Users who can perform all the technical actions in the database

Db_accessadmin
You can add, remove users from a user

Db_datareader
Users who can view data from user tables in all databases

Db_datawriter
Users who can add, modify, or delete data from user tables in all databases

db_ddladmin
Users who can perform all DDL operations in the database

Db_securityadmin
Users who can manage all actions related to security permissions in the database

Db_backoperator
Users who can back up the database (and can publish DBCC and CHECKPOINT statements, which are typically executed before the backup)

Db_denydatareader
Users who cannot see any data in the database

Db_denydatawriter
Users who cannot change any data in the database

Reference: http://www.cnblogs.com/dannyli/archive/2012/09/18/2690740.html

SQL Server servers and database roles