Roles

Roles, a new feature of SQL Server 7.0 which was not with earlier versions, perform like Windows NT local group. Server roles is one of the several roles supported by MS SQL Server, the others being database and application roles.

Server Roles

At the server level is defined the server roles which lie outside users databases. Server roles are fixed and cannot be added, deleted or modified. Users can only be added as server roles member.

Types

Fixed server roles are of seven types- sysadmin, serveradmin, setupadmin, securityadmin, processadmin, dbcreator and diskadmin. All activities in SQL Server can be performed by sysadmin server role members and overall database functions are completely controlled by them. The function of the members of the serveradmin server role is to change server configuration parameters and shutting down of the server. Setupadmin server role members can add or remove linked servers, manage replication and extended stored procedures. Some system stored procedures like sp serveroption can be executed by this role. Creation and management of server logins and auditing and reading error logs are done by securityadmin server role members. Management of the processes that run in SQL Server is done by processadmin server role members. Database creation, alteration and resizing are the functions of dbcreator server role members. Disk files are managed by diskadmin server role members.

Add/Remove Login

The syntax under sp_addsrvrolemember system stored procedure for addition of a login as a fixed server role member is: sp_addsrvrolemember [@loginame=] ‘login’, [@rolename=] ‘role’ where @loginame refers to either a SQL Server login or a Windows NT user account and @rolename refers to fixed server role name. Similarly the syntax for removal of a SQL Server login or a Windows NT user or a fixed server role group using sp_dropsrvrolemember system stored procedure is: sp_dropsrvrolemember [@loginame =] 'login', [@rolename =] 'role' where @loginame refers to the name of a login to remove and @rolename refers to the name of the fixed server role. A fixed server roles list can also be returned.