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