To add a member to a fixed server roleThe security mechanism in Microsoft® SQL Server™ includes several predefined roles with implied permissions that cannot be granted to other user accounts. If you have users who require these permissions to perform the special activities supported by these roles, you must add their accounts to these predefined roles. The two types of predefined roles are fixed server and fixed database.
Fixed server roles, such as sysadmin, are defined at the server level and exist outside of individual databases. To add a user as a member of a fixed server role, the user must have a SQL Server or Microsoft Windows NT® login account. Any member of a fixed server role can add other logins.
Important Windows NT users who are members of the BUILTIN\Administrators group are members of the sysadmin fixed server role automatically.
| Fixed server role | Description |
|---|---|
| sysadmin | Performs any activity in SQL Server. |
| serveradmin | Configures server-wide settings. |
| setupadmin | Adds/removes linked servers, and execute some system stored procedures, such as sp_serveroption. |
| securityadmin | Manages server logins. |
| processadmin | Manages processes running in SQL Server. |
| dbcreator | Creates and alters databases. |
| diskadmin | Manages disk files. |
The permissions of the sysadmin fixed server role span all of the other fixed server roles.
Note It is not possible to create new fixed server roles. Roles can be created only at the database level.
To add a member to a fixed server role
Fixed database roles, such as db_owner, are defined at the database level and exist in each database. You can add any valid user account (Windows NT user or group, or SQL Server user or role) as a member of a fixed database role. Each member gains the permissions applied to the fixed database role. Any member of a fixed database role can add other users to the role.
| Fixed database role | Description |
|---|---|
| db_owner | Performs the activities of all database roles, as well as other maintenance and configuration activities in the database. |
| db_accessadmin | Adds or removes Windows NT groups, Windows NT users, and SQL Server users in the database. |
| db_datareader | Sees all data from all user tables in the database. |
| db_datawriter | Adds, changes, or deletes data from all user tables in the database. |
| db_ddladmin | Adds, modifies, or drops objects in the database. |
| db_securityadmin | Manages roles and members of SQL Server database roles, and can manage statement and object permissions in the database. |
| db_backupoperator | Backs up the database. |
| db_denydatareader | Sees no data in the database. |
| db_denydatawriter | Changes no data in the database. |
The permissions of the db_owner fixed database role span all of the other fixed database roles.
To add a member to a SQL Server (fixed) database role