Adding a Member to a Predefined Role
The 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, you must add their accounts to these predefined roles. The two types of predefined roles are fixed server and fixed database.
Fixed Server Roles
Fixed server roles, which cannot be created, are defined at the server level and exist outside of individual databases. To add a user to a fixed server role, the user must have a SQL Server or Microsoft Windows NT® 4.0 or Windows® 2000 login account. Any member of a fixed server role can add other logins.
Important Windows NT 4.0 or Windows 2000 users who are members of the BUILTIN\Administrators group are members of the sysadmin fixed server role automatically.
The following table describes the fixed server roles.
| Fixed server role | Description |
|---|---|
| sysadmin | Performs any activity in SQL Server. The permissions of this role span all of the other fixed server roles. |
| serveradmin | Configures server-wide settings. |
| setupadmin | Adds and removes linked servers, and executes some system stored procedures, such as sp_serveroption. |
| securityadmin | Manages server logins. |
| processadmin | Manages processes running in an instance of SQL Server. |
| dbcreator | Creates and alters databases. |
| diskadmin | Manages disk files. |
| bulkadmin | Executes the BULK INSERT statement. |
The securityadmin has permission to execute the sp_password stored procedure for all users other than members of the sysadmin role.
The bulkadmin fixed server role has permission to execute BULK INSERT statements. Members of the bulkadmin role can add other logins to the role, as all members of any given fixed server role can do. However, due to the security implications associated with executing the BULK INSERT statement (the BULK INSERT statement requires read access to any data on the network and machine the server is running on), it may not be desirable for members of the bulkadmin role to grant permission to others. The bulkadmin role provides members of the sysadmin fixed server role with a method to delegate tasks requiring execution of the BULK INSERT statement, without granting users sysadmin rights. Members of the bulkadmin role are allowed to execute the BULK INSERT statement, but they still must have the INSERT permission on the table on which you wish to insert data.
To add a member to a fixed server role
Transact-SQLFixed Database Roles
Fixed database roles, which can be created, are defined at the database level and exist in each database. You can add any valid user account (a Windows NT 4.0 or Windows 2000 user or group, or a 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.
The following table describes the fixed database roles.
| Fixed database role | Description |
|---|---|
| db_owner | Performs the activities of all database roles, as well as other maintenance and configuration activities in the database. The permissions of this role span all of the other fixed database roles. |
| db_accessadmin | Adds or removes Windows NT 4.0 or Windows 2000 groups and 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 (runs all DDLs). |
| db_securityadmin | Manages roles and members of SQL Server 2000 database roles, and manages statement and object permissions in the database. |
| db_backupoperator | Has permission to back up the database. |
| db_denydatareader | Denies permission to select data in the database. |
| db_denydatawriter | Denies permission to change data in the database. |
To add a member to a SQL Server (fixed) database role