SqlServer server and database roles

First, we will describe the server (instance-level) permissions. The biggest difference between instance-level and database-level permissions is that the instance-level permissions are directly authorized to the login name, the database-level full display is granted to the database user, and then the database user matches with the login name. (In SqlServer, the login name and user are two concepts. The login name is used to log on

First, we will describe the server (instance-level) permissions. The biggest difference between instance-level and database-level permissions is that the instance-level permissions are directly authorized to the login name, the database-level full display is granted to the database user, and then the database user matches with the login name. (In SqlServer, the login name and user are two concepts. The login name is used to log on

First, we will describe the server (instance-level) permissions. The biggest difference between instance-level and database-level permissions is that the instance-level permissions are directly authorized to the login name, the database-level full display is granted to the database user, and then the database user matches with the login name. (In SqlServer, the login name and user are two concepts. The login name is used to log on to the database instance, while the user is located in the database and matched with the login name)

Example:

-- Specify the login name as dbtester and create user tester1 in the test Database
Execute sp_grantdbaccess 'dbster', 'tester1'

You can grant different permissions to different objects within the server (Instance) scope, including the endpoints, logon names, high availability groups, and custom server roles, fixed server role. Use the sys. server_principals and sys. server_permissions views to view the granted permissions.

Example:

Note that the Control Server grants the user the super administrator permission, which gives the user the highest Control over SqlServer. Therefore, do not grant this permission.

The following describes the roles of a fixed Server:

Sysadmin
The execution of any action in SQL Server is also the permission for any operation on any database.

Serveradmin
Configure server settings and use SHUTDOWN to stop instance permissions.

Setupadmin
Add another login, add, delete, or configure link to the server role, and execute some system processes, such as sp_serveroption.

Securityadmin
The logon name is used to manage logon names, read error logs, and create database permission. You can perform all actions on server access and security.

Processadmin
Manage SQL Server Processes and kill the permissions of other user processes in the instance.

Dbcreator
Create and modify Databases

Diskadmin
Permission for managing physical databases.
Bulkadmin

Batch Data insertion permission to the database (added in SqlServer2005)

Public

No permissions are granted to other members. Only the connection permission is required.

Fixed database roleDefined on the database layer, so they exist in each database on the database server. Lists All fixed database roles.

Db_owner
Users who can perform all technical actions in the database

Db_accessadmin
Users who can add or delete users

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

Db_datawriter
Users who can add, modify, or delete data in 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 generally executed before 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