quote:

---

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

Enterprise Manager


How to add a member to a fixed server role (Enterprise Manager)
To add a member to a fixed server role

Expand a server group, and then expand a server.


Expand Security, and then click Server Roles.


In the details pane, right-click the role, and then click Properties.


On the General tab, click Add, and then click the logins to add.

See Also

Adding a Member to a Predefined Role


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


How to add a member to a SQL Server database role (Enterprise Manager)
To add a member to a SQL Server database role

Expand a server group, and then expand a server.


Expand Databases, and then expand the database in which the role exists.


Click Roles.


In the details pane, right-click the role to which the user will be added, and then click Properties.


Click Add, and then click a user or users to add.
Only users in the selected database can be added to the role.


See Also

Adding a Member to a Predefined Role

Adding a Member to a SQL Server Database Role


SQL-DMO

©1988-2000 Microsoft Corporation. All Rights Reserved.

---

quote:

---

sp_addsrvrolemember
Adds a login as a member of a fixed server role.

Syntax
sp_addsrvrolemember [ @loginame = ] 'login'
, [ @rolename = ] 'role'

Arguments
[@loginame =] 'login'

Is the name of the login being added to the fixed server role. login is sysname, with no default. login can be a Microsoft® SQL Server™ login or a Microsoft Windows NT® user account. If the Windows NT login has not already been granted access to SQL Server, access is granted automatically.

[@rolename =] 'role'

Is the name of the fixed server role in which the login is being added. role is sysname, with a default of NULL, and must be one of these values:

sysadmin


securityadmin


serveradmin


setupadmin


processadmin


diskadmin


dbcreator


bulkadmin
Return Code Values
0 (success) or 1 (failure)

Remarks
When a login is added to a fixed server role, the login gains the permissions associated with that fixed server role.

The role membership of the sa login cannot be changed.

Use sp_addrolemember to add a member to a fixed database or user-defined role.

sp_addsrvrolemember stored procedure cannot be executed within a user-defined transaction.

Permissions
Members of the sysadmin fixed server role can add members to any fixed server role. Members of a fixed server role can execute sp_addsrvrolemember to add members only to the same fixed server role.

Examples
This example adds the Windows NT user Corporate\HelenS to the sysadmin fixed server role.

EXEC sp_addsrvrolemember 'Corporate\HelenS', 'sysadmin'


See Also

sp_addrolemember

sp_dropsrvrolemember

System Stored Procedures

©1988-2000 Microsoft Corporation. All Rights Reserved.

---