Chapter2UserAuthentication, Authorization, andSecurity (5 ):

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');