Adding a Member to a Predefined Role

SQL Server Setup Help

SQL Server Setup Help

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