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