--- PDF PAGE 349 ---
334
You can set the authentication mode in SQL Server Management Studio within the 
Security tab of the Server Properties dialog box. You will need to restart the SQL Server 
service for the change to take effect.
Server Roles
SQL Server provides a set of server roles, out of the box, that allow you to assign instance-­
level permissions to logins that map to common requirements. These are called fixed 
server roles, and you cannot change the permissions that are assigned to them; you can 
only add and remove logins. Table 10-1 describes each of these fixed server roles.
Table 10-1.  Fixed Server Roles
Role
Description
sysadmin
The sysadmin role gives administrative permissions to the entire instance.  
A member of the sysadmin role can perform any action within the instance  
of the SQL Server relational engine.
blkadmin
In conjunction with the INSERT permission on the target table within a 
database, the bulkadmin role allows a user to import data from a file using 
the BULK INSERT statement. This role is normally given to service accounts 
that run ETL processes.
dbcreator
The dbcreator role allows its members to create new databases within the 
instance. Once a user creates a database, he is automatically the owner of that 
database and is able to perform any action inside it.
diskadmin
The diskadmin role gives its members the permissions to manage backup 
devices within SQL Server.
processadmin
Members of the processadmin role are able to stop the instance from T-SQL 
or SSMS. They are also able to kill running processes.
public
All SQL Server logins are added to the public role. Although you can assign 
permissions to the public role, this does not fit with the principle of least 
privilege. This role is normally only used for internal SQL Server operations, 
such as authentication to TempDB.
(continued)
Chapter 10  SQL Server Security Model

--- PDF PAGE 350 ---
335
Role
Description
securityadmin
Members of the securityadmin role are able to manage logins at the 
instance level. For example, members may add a login to a server role (except 
sysadmin) or assign permissions to an instance-level resource, such as 
an endpoint. However, they cannot assign permissions within a database to 
database users.
serveradmin
Serveradmin combines the diskadmin and processadmin roles. As well 
as being able to start or stop the instance, however, members of this role can 
also shut down the instance using the SHUTDOWN T-SQL command. The subtle 
difference here is that the SHUTDOWN command gives you the option of not 
running a CHECKPOINT in each database if you use it with the NOWAIT option. 
Additionally, members of this role can alter endpoints and view all instance 
metadata.
setupadmin
Members of the setupadmin role are able to create and manage linked 
servers.
Table 10-1.  (continued)
You can also create your own server roles, which group users who need a common 
set of permissions that are tailored to your environment. For example, if you have a 
highly available environment that relies on availability groups, then you may wish to 
create a server role called AOAG and grant this group the following permissions:
•	
Alter any availability group
•	
Alter any endpoint
•	
Create availability group
•	
Create endpoint
You can then add the junior DBAs, who are not authorized to have full sysadmin 
permissions, but who you want to manage the high availability of the instance, to this 
role. You can create this server role by selecting New Server Role from the context menu 
of Security ➤ Server Roles in SSMS. The General tab of the New Server Role dialog box is 
illustrated in Figure 10-2.
Chapter 10  SQL Server Security Model