In this article, I want to tell you about each kind of roles, about how you can add new role, how you can drop existing role, how you can return information about the roles, and so on.

Server Roles

There are seven fixed server roles:

The members of sysadmin server role can perform any activity in SQL Server and have completes control over all database functions.

The members of serveradmin server role can change server configuration parameters and shut down the server.

The members of setupadmin server role can manage linked servers (add or remove linked servers), manage replication, manage extended stored procedures, and execute some system stored procedures, such as sp_serveroption.

The members of securityadmin server role can create and manage server logins and auditing, and read error logs.

The members of processadmin server role can manage the processes running in SQL Server.

The members of dbcreator server role can create, alter, and resize databases.

The members of diskadmin server role can manage disk files.

Note. To add a login as a member of a fixed server role, you can use the sp_addsrvrolemember system stored procedure.

This is the syntax:

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

where

@loginame - is a SQL Server login or a Windows NT user account.
@rolename - is the name of the fixed server role.

See this link for more information: sp_addsrvrolemember (T-SQL)

Note. To remove a SQL Server login or a Windows NT user or group from a fixed server role, you can use the sp_dropsrvrolemember system stored procedure.

This is the syntax:

sp_dropsrvrolemember [@loginame =] 'login', [@rolename =] 'role'

where

@loginame - is the name of a login to remove.
@rolename - is the name of the fixed server role.

See this link for more information: sp_dropsrvrolemember (T-SQL)

Note. To return a list of the SQL Server fixed server roles, you can use the sp_helpsrvrole system stored procedure.

This is the syntax:

sp_helpsrvrole [[@srvrolename =] 'role']

where

@srvrolename - is the name of the fixed server role.

See this link for more information: sp_helpsrvrole (T-SQL)

Database Roles

There are three kinds of the database roles:

Fixed Database Roles

There are nine fixed database roles:

The members of db_owner database role can perform any activity in the database.

The members of db_accessadmin database role can add or remove Windows NT groups, users or SQL Server users in the database.

The members of db_datareader database role can see any data from all user tables in the database.

The members of db_datawriter database role can add, change, or delete data from all user tables in the database.

The members of db_ddladmin database role can make any data definition language commands in the database.

The members of db_securityadmin database role can manage statement and object permissions in the database.

The members of db_backupoperator database role can back up the database.

The members of db_denydatareader database role can deny permission to select data in the database.

The members of db_denydatawriter database role can deny permission to change data in the database.

Note. To add a security account as a member of an existing SQL Server database role in the current database, you can use the sp_addrolemember system stored procedure.

This is the syntax:

sp_addrolemember [@rolename =] 'role',
    [@membername =] 'security_account'

@rolename - is the name of the database role.
@membername - is the name of the security account.

Any member of a fixed database role can add other users to this role.
See this link for more information: sp_addrolemember (T-SQL)

Note. To remove a security account from a SQL Server role in the current database, you can use the sp_droprolemember system stored procedure.

This is the syntax:

sp_droprolemember [@rolename =] 'role',
    [@membername =] 'security_account'

@rolename - is the name of the role.
@membername - is the name of the security account.

See this link for more information: sp_droprolemember (T-SQL)

Note. To return information about the members of a role in the current database, you can use the sp_helprolemember system stored procedure.

This is the syntax:

sp_helprolemember [[@rolename =] 'role']

where

@rolename - is the name of a role in the current database.

See this link for more information: sp_helprolemember (T-SQL)

Public Role

User-Defined Database Roles

Note. To create a new SQL Server role in the current database, you can use the sp_addrole system stored procedure.

This is the syntax:

sp_addrole [@rolename =] 'role' [,[@ownername =] 'owner']

where

@rolename - is the name of the database role.
@ownername - is the owner of the new role.

See this link for more information: sp_addrole (T-SQL)

Note. To remove a SQL Server role from the current database, you can use the sp_droprole system stored procedure.

This is the syntax:

sp_droprole [@rolename =] 'role'

where

@rolename - is the name of the role.

See this link for more information: sp_droprole (T-SQL)

Application Roles

Note.To add a special type of role in the current database, which is used for application security, you can use the sp_addapprole system stored procedure.

This is the syntax:

sp_addapprole [@rolename =] 'role', [@password =] 'password'

where

@rolename - is the name of the application role.
@password - is the password for the new application role.

See this link for more information: sp_addapprole (T-SQL)

Note. To remove an application role from the current database, you can use the sp_dropapprole system stored procedure.

This is the syntax:

sp_dropapprole [@rolename =] 'role'

where

@rolename - is the name of the application role.

See this link for more information: sp_dropapprole (T-SQL)

Note. To change the password of an application role in the current database, you can use the sp_approlepassword system stored procedure.

This is the syntax:

sp_approlepassword [@rolename =] 'role', [@newpwd =] 'password'

where

@rolename - is the name of the application role.
@newpwd - is the new password for the application role.

See this link for more information: sp_approlepassword (T-SQL)