Server roles apply server-wide and the roles themselves cannot be
changed, only applied to logins. The following server roles exist:
| Server Role |
Permission |
| sysadmin |
Can perform any activity. |
| serveradmin |
Can configure the server-wide settings. |
| setupadmin |
Can manage extended stored procedures. |
| securityadmin |
Can manage the logins for the server. |
| processadmin |
Can manage the processes running in SQL Server. |
| dbcreator |
Can create and alter databases. |
| diskadmin |
Can manage the disk files. |
Assigning Server Roles in Enterprise Manager
- In Enterprise Manager, find the desired SQL Server instance.
- Go to Security and then Server Roles.
- In the right-hand panel, right-click the desired role and select
Properties.
In the General tab, click the Add... button and
select the logins that you wish to assign the selected role. The
Permissions tab lists the actions that the selected role can perform.
Assigning Server Roles with T-SQL
You can assign server roles by running the following command:
sp_addsrvrolemember [@loginame =] 'login', [@rolename =] 'role'
You can remove server roles with the following command:
sp_dropsrvrolemember [@loginame =] 'login', [@rolename =] 'role'
where login is the login name
you wish to add/remove, and role is
the role you are assigning/removing.