Chapter2UserAuthentication, Authorization, andSecurity (5 ):

Source: Internet
Author: User
Source: blog. csdn. netdba_huangzjarticledetails38844999, topic Directory: blog. csdn. netdba_huangzjarticledetails37906349 no one shall publish in the original form or use it for commercial purposes without the consent of the author. I am not responsible for any legal liability. Previous Article: B

Source of the original article: without the consent of the author, no one shall publish in the original form or use it for commercial purposes. I am not responsible for any legal liability. Previous Article: http: // B

Source: Workshop

Without the consent of the author, no one shall be published in the form of "original" or used for commercial purposes. I am not responsible for any legal liability.

Previous Article: http://blog.csdn.net/dba_huangzj/article/details/38817915

Preface:

The Logon account allows you to connect to SQL Server. If a database user maps to this account, this account can also access the corresponding database. By default, they do not have server-level management permissions. The fixed server role allows you to simplify authorization and revoke permissions.

Implementation:

1. Open the logon attribute box and select the server role page:

The following server roles are displayed:

Their functions are described as follows:

Role name Description
BulkadminBulkadmin Members of a fixed server role can run the bulk insert statement.
Dbcreator Members of a fixed server role can create, change, delete, and restore any database.
Diskadmin Members of a fixed server role can manage disk files.
Processadmin A member of a fixed server role can terminate a process running in a database engine instance.
Public By default, all SQL Server users, groups, and roles belong to the public fixed Server role.
Securityadmin Members of a fixed server role can manage the login name and its attributes.

They can GRANT, DENY, and REVOKE server-level permissions.

They can also GRANT, DENY, and REVOKE database-level permissions.

In addition, they can reset the password of the SQL Server login name.

Serveradmin Members of a fixed server role can change the server range configuration options and disable the server.
Setupadmin Fixed server role members can add or delete linked servers and execute certain system stored procedures.
Sysadmin Members of a fixed server role can execute any activity in the database engine.

2. You can use the following statement to add a server role member:

ALTER SERVER ROLE 
 
   ADD MEMBER 
  
   ; 
  
 

3. You can use the following statement to view role members:

SELECT  role.name AS role ,         role.is_fixed_role ,         login.name AS login FROM    sys.server_role_members srm         JOIN sys.server_principals role ON srm.role_principal_id = role.principal_id         JOIN sys.server_principals login ON srm.member_principal_id = login.principal_id;
Principle:

By adding members to a server role, you can use the preset management permissions in the role. The public role is introduced from 2005. Each Logon account is automatically added to this role and cannot be removed from this role and its members. Unlike other fixed server roles, you can modify the public permissions to "initialize" the default permissions of all accounts. You can use the following statement to view the public role permissions:

SELECT permission_name, state_desc, SUSER_NAME(grantor_principal_id) grantor FROM sys.server_permissions WHERE grantee_principal_id = SUSER_ID('public');

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.