SQL Server Add user

1. After the SA user logs in, create a new login in security

2, add the login name, the following default database select the default database that the user can access

3. Select public in the server role

4. Select the database that the user can access in the user mapping, the database role generally chooses public and db_owner

Reference Document: Http://msdn.microsoft.com/zh-cn/library/ms188659.aspx

Server-level roles

To help you manage permissions on the server, SQL Server provides several roles. These roles are security principals that are used to group other principals. Permission scopes for server-level roles are server-wide. ("role" is similar to "group" in Windows operating system.) ）

Fixed server roles are provided for ease of use and backwards compatibility. More specific permissions should be assigned whenever possible.

SQL Server provides nine fixed server roles. The permissions granted to the fixed server role cannot be changed. Starting with SQL Server 2012, you can create user-defined server roles and add server-level permissions to user-defined server roles.

You can add server-level principals (SQL Server logins, Windows accounts, and Windows groups) to server-level roles. Each member of the fixed server role can add additional logins to the same role. A member of a user-defined server role cannot add additional server principals to the role.

The following table shows the fixed roles and their permissions at the server level.

Fixed roles at the server level

Description

Sysadmin	Members of the sysadmin fixed server role can perform any activity on the server.
ServerAdmin	Members of the ServerAdmin fixed server role can change server-wide configuration options and shut down the server.
Securityadmin	Securityadmin members of the 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 if they have access to the database. In addition, they can reset the password for the SQL Server login. Safety Instructions The ability to grant access to the database engine and to configure user permissions allows security administrators to assign most server permissions. The securityadmin role should be considered equivalent to the sysadmin role.
Processadmin	A member of the Processadmin fixed server role can terminate a process that runs in an instance of SQL Server.
Setupadmin	Members of the Setupadmin fixed server role can add and remove linked servers.
Bulkadmin	Members of the Bulkadmin fixed server role can run BULK INSERT statements.
Diskadmin	The Diskadmin fixed server role is used to manage disk files.
DBCreator	Members of the dbcreator fixed server role can create, change, delete, and restore any database.
Public	Each SQL Server logon name belongs to the public server role. If a server principal is not granted or denied specific permissions to a securable object, the user inherits the permissions granted to the public role of the object. When you want the object to be available to all users, you only need to assign public permissions to any object. You cannot change a member relationship in public. Note Public is implemented in a different way than other roles. However, permissions can be granted, denied, or revoked from public.

Database-level roles

To facilitate the management of permissions in the database, SQL Server provides several roles, which are security principals that are used to group other principals. They are similar to groups in the Microsoft Windows operating system. Permission scopes for database-level roles are database-scoped.

There are two types of database-level roles in SQL Server: The predefined fixed database roles in the database and the flexible database roles that you can create.

Fixed database roles are defined at the database level and exist in each database. Members of the db_owner and db_securityadmin database roles can manage fixed database role memberships. However, only members of the db_owner database role can add members to the db_owner fixed database role. There are also special purpose fixed database roles in the msdb database.

You can add any database account and other SQL Server roles to the database-level role. Each member of a fixed database role can add additional logins to the same role.

Important Notes

Do not add a flexible database role as a member of a fixed role. This can cause unexpected permissions escalation.

The following table shows the fixed database-level roles and the actions they can perform. These roles are available in all databases.

Database-level role names

Description

db_owner	Members of the db_owner fixed database role can perform all configuration and maintenance activities for the database, and can also delete the database.
Db_securityadmin	Members of the db_securityadmin fixed database role can modify role memberships and administrative permissions. Adding a principal to this role may result in unexpected permission escalation.
Db_accessadmin	Members of the db_accessadmin fixed database role can add or remove database access permissions 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) commands in the 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 from user tables in the database.
Db_denydatareader	Members of the db_denydatareader fixed database role cannot read any data from user tables in the database.

msdb role

The msdb database contains the special-purpose roles that are shown in the following table.

msdb Role Name

Description

Db_ssisadmin Db_ssisoperator Db_ssisltduser	Members of these database roles can manage and use SSIS. An instance of SQL Server that was upgraded from an earlier version might contain an older version of the role named using Data transformation Services (DTS) instead of SSIS. For more information, see Integration Services role (early SSIS service).
Dc_admin Dc_operator Dc_proxy	Members of these database roles can manage and use the Data collector. For more information, see Data collection.
Policyadministratorrole	Members of the db_ policyadministratorrole database role can perform all configuration and maintenance activities on policy-based management policies and conditions. For more information, see Managing servers with policy-based management.
Servergroupadministratorrole Servergroupreaderrole	Members of these database roles can manage and use registered server groups.
Dbm_monitor	Created in the msdb database when the first database is registered in the database Mirroring Monitor. The role does not have any members until the system administrator assigns users to the dbm_monitor role.

Important Notes

Members of the Db_ssisadmin role and dc_admin role can elevate their privileges to sysadmin. Because these roles can modify the Integration Services package, SQL Server can implement the Integration Services package by using the sysadmin security context of SQL Server Agent, thereby enabling elevation of privilege. To prevent elevated privileges when running maintenance plans, data collection sets, and other integration Services packages, configure the SQL Server Agent job that runs the package as a proxy account with limited privileges, or add only the sysadmin members to Db_ssisadmin and D C_admin role.

SQL Server Add user