SQL Server server role

Source: Internet
Author: User

All server roles are "fixed" roles, and they exist from the start-all of the server roles you will have have existed since the moment the SQL Server was installed.

Role
Characteristics

Sysadmin
This role is capable of performing any operation on SQL Server. In essence, any person with this role membership is the SA on that server. The creation of this server role provided Microsoft with the ability to remove the sa login one day-in fact, Books Online referred to SA as essentially a relic. It is important to note that on SQL Server, The Windows Administrators group is automatically mapped to the sysadmin role. This means that any member of the server's Administrators group also has access to the SA level of the SQL data. If you want, you can remove the Windows Administrators group from the sysadmin role to improve security, protect against vulnerabilities

ServerAdmin
This role can set server-wide configuration options or shut down the server. Although it is fairly limited in scope, the functionality that is controlled by members of the role can have a significant impact on the performance of the server

Setupadmin
This role is limited to managing linked servers and the startup process

Securityadmin
This role is convenient for logins specifically created to manage logins, read error logs, and create DATABASE permissions. In many ways, the role is a typical system operator role-it can handle most of the daily tasks, but it does not have the kind of global access that a truly omnipotent super user has.

Processadmin
Ability to manage processes running in SQL Server-the role can terminate long-running processes, if necessary

DBCreator
This role is limited to creating and changing databases

Diskadmin
Manage disk files (what filegroups are assigned, attach and detach databases, and so on)

Bulkadmin
The character is a bit weird. It is explicitly created to execute the permissions of the BULK INSERT statement, otherwise the BULK INSERT statement can only be executed by someone with sysadmin privileges. Frankly, I don't understand why the statement does not grant permissions through the grant command, as other things do, but it does not. Keep in mind that even if a user is added to the Bulkadmin group, it only gives them permission to access that statement, and the table that runs the statement does not give the user permission to access that table. This means not only adding users to bulkadmin, but also granting the (grant) User Insert permission to the table that you want the user to be able to execute BULK insert on. Also, for all tables that will be referenced in the BULK INSERT statement, make sure that the user has the correct select access to those tables.

You can mix and match these roles for individual users who assume management role tasks on the server. In general, I suspect that only the largest database will use more roles than the sysadmin and securityadmin, however, it is convenient to have them next to each other.

SQL Server server role

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.