In previous versions of SQL Server, the user-defined role-based security are there in order to achieve access rights as per requirement. The user-defined role customized roles at the database level, not at the server level. This is because at the server level, administrators had to use fixed roles. Server roles were fixed and could not be customize as per different security requirements. This led to database administrators providing users to a higher roles ,such as the sysadmin role, because available server roles are not meeting up with business requirement.
SQL Server 2012, user-defined roles have been introduced at the server level also to increase flexibility, increase manageability and for better control & compliance follow-up of access rights as per requirement.
How to create a server role with SSMS:
1. In SQL Server Management Studio, use Object Explorer to connect to an instance of the SQL Server Database Engine.
2. In Object Explorer, expand the instance of SQL Server, and expand the Security folder.
3. Right-click the Server Roles folder, and select New Server Role.
4. On the General page of the New Server Role Wizard, do the following:
a. Specify the name of the new server role.
b. Select the owner for the new server role.
c. Choose the appropriate securables as they mention to the new server role.
d. When a securable is selected, apply explicit permission by selecting the checkbox for one of the following permissions: Grant, With Grant, or Deny.
5. On the Members page, add logins that represent individuals or groups to be added to one or more server roles.
6. Finally, on the Memberships page, because a user-defined server role can be a member of
another server role, specify the appropriate server roles that the new server Role will be a
member of.
