Chapter2UserAuthentication, Authorization, andSecurity (7 ):

Source: blog. csdn. netdba_huangzjarticledetails38895357, 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/38867489

Preface:

Starting from SQL Server 2012, you are no longer limited to using fixed Server roles. You can create custom roles that allow you to set special permissions.

Implementation:

1. In SSMS, select security node, right-click server role node, and select new server role ]:

2. enter a new role name.

3. You can authorize the following object types:

Security object	Description
Endpoints (endpoint)	View definition, change, take over ownership, control, connection
Logins (login name)	View definition, change, control, and Simulation
Servers (server)	Server-level Permissions
Availability Groups (Availability Group)	Availability Group Management Right
Server Roles)	Allows you to manage custom server roles of other users.

For example, to create a server role that allows consultants to adjust performance, you can use the following code:

CREATE SERVER ROLE PerformanceTuning; GRANT VIEW SERVER STATE TO PerformanceTuning; GRANT ALTER TRACE TO PerformanceTuning; GRANT ALTER ANY EVENT SESSION TO PerformanceTuning; GRANT VIEW ANY DEFINITION TO PerformanceTuning; GRANT VIEW ANY DATABASE TO PerformanceTuning; GO ALTER SERVER ROLE PerformanceTuning ADD MEMBER Fred; 

The login name of this role, such as Fred, has permissions to run SQL Trace (Profiler), query DMV, expand events, and view data and code definitions.

Principle:

The user-defined server role grants specific permissions to all login names belonging to this role through preset permissions. You cannot use the OLD_PASSWORD clause to change the password for the CONTROL permission of the Logon account. Only the control server permission is allowed.

You can use the following statement to view the server permissions that you can add to the role:

SELECT * FROM sys.fn_builtin_permissions('SERVER') ORDER BY permission_name;

To check whether a login name is a member of a server role, use the following statement:

-- Is the current logon name a member of processadmin? SELECT IS_SRVROLEMEMBER ('processadmin'); -- is the logon name Fred a member of The 'endpoints _ admin' User-Defined server role? SELECT IS_SRVROLEMEMBER ('endpoints _ admin', 'fred ');

More:

DDL trigger events allow you to monitor changes to server role members. The following is an example of storing server role modification records in a database named dbo. the table of auditServerRole, which is under AuditDB (pre-created), will be detailed in chapter 7. Here only demonstrates:

CREATE TRIGGER serverRoleMembershipChange ON ALL SERVER FOR ADD_SERVER_ROLE_MEMBER, DROP_SERVER_ROLE_MEMBER AS BEGIN     INSERT INTO auditdb.dbo.auditServerRole (when, who, what)     SELECT CURRENT_TIMESTAMP, SYSTEM_USER,     EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText) [1]','nvarchar(max)') ; END;

Next article: http://blog.csdn.net/dba_huangzj/article/details/38944121