How to assign a specific set of permissions on the SQL Server instance to a SQL Server login.

Problem: How to assign a specific set of permissions on the SQL Server instance to a SQL Server login.

Solution: Server Roles

Server roles enable you to simplify the assignment of permissions at the database instance level. Although it is possible to assign permissions to SQL logins, this can be difficult to manage in all but the smallest environments. Permissions are assigned to server roles rather than to individual SQL logins. To grant a set of permissions to a specific SQL login, add the login to a server role. SQL Server 2012 ships with nine built-in server roles. These built-in server roles are fixed and, other than the public role, it is not possible to modify the permissions assigned to these roles. In SQL Server 2012, you can modify the permissions assigned to a new type of server role known as a user-defined server role.

The nine-fixed server-level roles are as follows:

1.       sysadmin Role members can perform all activities possible on the Database Engine instance. You specify initial membership of this role when performing installation of the Database Engine feature.
2.          serveradmin Role members can perform instance-wide configuration tasks. Members of this role can shut down the instance.
3.        securityadmin Assign this role to logins that must be able to manage instance-level permissions. Because this role can configure permissions at the instance level, membership in this role allows the elevation of privileges on logins and user-defined server roles to the equivalent of those assigned to the sysadmin fixed server role.
4.         processadmin Role members can terminate processes running on a Database Engine instance.
5.          setupadmin Role members can add linked servers to and remove linked servers from the Database Engine instance.
6.         bulkadmin Assign this role to logins that you want to allow to use the BULK INSERT statement on databases hosted on an instance.
7.            diskadmin Role members can manage instance-related files.
8.         dbcreator Role members can create, alter, drop, and restore databases hosted on the Database Engine instance.
9.        public All SQL Server logins are members of this role. You can alter the permissions assigned to this role, but you cannot alter the membership of the role.

Below is the image of how this set of server roles are mapped in each permissions. Picture is used from Microsoft reference.

Example: In this example, I would like to show we can add a login to a server role using ssms and T-SQL.

Step 1: Connect to the database engine and go to the server roles folder of the instance and double click on the role you want to assign to the login and Click Add button.

Step 2: Browse the login you want to add and click OK.

T-SQL: The below line of code will do the same as we did from management studio.

ALTER SERVER ROLE [sysadmin] ADD MEMBER [sql_user]