Export (0) Print
Expand All

Adding a Member to a Predefined Role

SQL Server 2000

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-SQL

Enterprise Manager

SQL-DMO

Fixed 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

Transact-SQL

Enterprise Manager

SQL-DMO

Was this page helpful?
(1500 characters remaining)
Thank you for your feedback
Show:
© 2014 Microsoft