At Access control of MS SQL – Part 1, we tell about “Logins” & “Users”. In this post, we will take about “Server Roles”

In SQL Server, there are 9 fixed server roles. Each of them are assigned some unchangeable permission except “public”
1. sysadmin can do anything within the SQL Server.
2. serveradmin can shut-down and change server-wide configuration setting.
3. securityadmin can manage “Logins” and assign server & database level permission.
4. processadmin can kill connections to SQL Server.
5. setupadmin can add & remove linked server by using T-SQL.
6. bulkadmin can execute “BULK INSERT”
7. diskadmin can manage disk files.
8. dbcreator can create, alter, drop and restore database
9. public can assign permission to it. It is default role assign to all “Logins” and it’s member list is unchangeable.

How to assign Fixed server roles to “Logins” at “Logins” via GUI:
1. Expand “Security” > Expand “Logins” > Right-click suitable login > select “Properties”
2. Select “Server Roles” > click suitable server roles > click “OK”

How to assign Fixed server roles to “Logins” at “Server Roles” via GUI:
1. Expand “Security” > Right-click suitable “Server Roles” > select “Properties”
2. Click “Add” to add “Logins” to this server roles
P.S.: If you right-click “public“, you will not find any way to manage its member, it only show the permission of “public” have.

How to assign Fixed server roles to “Logins” via GUI:

-- Add "joeschoice\mssql-operator" to "sysadmin"
EXEC sp_addsrvrolemember 'JOESCHOICE\mssql-operator', 'sysadmin';
-- remove "joeschoice\mssql-operator" from "sysadmin"
EXEC sp_dropsrvrolemember 'JOESCHOICE\mssql-operator', 'sysadmin';