| System Administrators(sysadmin) |
This role is all encompassing and can do anything in SQL Server without setting
any object permission. Use of this role should be highly protected and not assigned
to logins without extreme justification. |
| Server Administrators(serveradmin) |
This role is used to set server-wide configuration options and shut down
servers. The logins assigned to this role also have the ability to add other logins
to this role. |
| Setup Administrators(setupdamin) |
This role can manage lined servers and all startup procedures. The logins
assigned to this role also have the ability to add other logins to this role. |
| Security Administrators(securityadmin) |
This role can manage logins and CREATE DATABASE permission, read error logs
and change passwords. The logins assigned to this role also have the ability to
add other logins to this role. |
| Process Administrators(processadmin) |
This role can manage processes running in SQL Server. The logins assigned
to this role also have the ability to add other logins to this role. |
| Database Creators(dbcreator) |
This role can create, alter and drop databases. The logins assigned to this
role also have the ability to add other logins to this role. |
| Disk Administrators(diskadmin) |
This role can manage disk files. The logins assigned to this role also have
the ability to add other logins to this role. |
| Bulk Administrators(bulkadmin) |
This role can execute BULK INSERT statements. The logins assigned to this
role also have the ability to add other logins to this role. |
| Database Owner(db_owner) |
This database role is all encompassing and has all permissions in the database. |
| Database Access Administrators(db_accessadmin) |
This role can add or remove Windows groups or SQL Server users to the database. |
| Database Security Administrators(db_securityadmin) |
This role can manage all permissions, object ownership, roles and role memberships. |
| Database DDL Administrators(db_ddladmin) |
This role can execute all data definition language (ddl) statements but cannot
issue GRANT, REVOKE or DENY statements. |
| Database Backup Operators(db_backupoperator) |
This role can execute DBCC, CHECKPOINT, and BACKUP statements. |
| Database Data Reader(db_datareader) |
This role can SELECT any data in any user table in the database. |
| Database Data Writer(db_datawriter) |
This role can INSERT, UPDATE, or DELETE any data in any user table in the
database. |
| Database Deny Data Reader(db_denydatareader) |
This role cannot SELECT any data in any user table in the database. |
| Database Deny Data Writer(db_denydatawriter) |
This role cannot INSERT, UPDATE, or DELETE any data in any user table in
the database. |
| Public(public) |
This is a special role in which every database user belongs and is primarily
used by SQL Server to give permissions to users to use objects needed by SQL Server
to authenticate the user, let the user use Enterprise Manager, etc. |
| sysadmin |
This role can enable, modify, or drop distributors, publishers, and subscribers;
create, drop, or modify a publication and its properties; create or delete a push
or pull subscription; update a PAL (publication access list); enable snapshots
for FTP downloading using the Internet; configure agent profiles; monitor replication
agents; configure agent profiles; cleanup replication tasks; and schedule replication
jobs. |
| db_owner |
This role can create or drop, create or modify a publication or its properties;
create or delete a pull or push subscription; update a PAL; enable snapshots for
FTP downloading using the Internet; cleanup replication tasks; and schedule replication
jobs. |
sp_addsrvrolemember
|
System stored procedure that adds a login as a member of a fixed server role.
Permissions default to members of the sysadmin server role that can add
members to any fixed server role and members of a fixed server role to add members
only to the same fixed server role. |
sp_dropsrvrolemember
|
System stored procedure that removes a SQL Server login or a Windows NT user
or group from a fixed server role. Permissions default to members of sysadmin
fixed server role to remove members of any server role and members each server
role that can remove other members of the same server role. Permissions are not
transferable. |
sp_helpsrvrolemember
|
System stored procedure that returns information about the members of a SQL
Server fixed server role. |
sp_srvrolepermission
|
System stored procedure that returns the permissions applied to a fixed server
role. |
sp_helpsrvrole
|
System stored procedure that returns a list of the SQL Server fixed server
roles. |
| IS_SRVROLEMEMBER
|
Security function that returns an integer indicating whether the current
user login is a member of the specified server role. |
sp_addrolemember
|
System stored procedure that adds a security account as a member of an existing
SQL Server database role in the current database. Permissions default to members
of the sysadmin server role and the db_owner database role to add
members to fixed database roles or user-defined roles. Owners of user-defined
roles can add members to the roles they own and members of the db_securityadmin
database role can add users to any user-defined role. |
sp_droprolemember
| System stored procedure that removes a security account from a SQL Server
role in the current database. Permissions default to members of the sysadmin
server role and the db_owner and db_securityadmin database and are
not transferable and only a member of the sysadmin> fixed server role or the db_owner
fixed database role can remove users from a fixed database role.
|
sp_dbfixedrolepermission
|
System stored procedure that displays the permissions for each fixed database
role. |
sp_helpdbfixedrole
|
System stored procedure that returns a list of the fixed database roles. |
sp_helprole
|
System stored procedure that returns information about the roles in the current
database. |
| IS_MEMBER
|
Security function that returns an integer indicating whether the current
user is a member of the specified NT group or SQL Server role. |
sp_addrole
|
System stored procedure that creates a new SQL Server role in the current
database. Permissions default to members of the sysadmin server role, and
the db_securityadmin and db_owner database roles and are not transferable. |
sp_droprole
|
System stored procedure that removes a SQL Server role from the current database.
Permissions default to members of the sysadmin server role, the db_owner
and db_securityadmin database roles, or the owner of the role and are not
transferable. |
sp_addrolemember
|
System stored procedure that adds a security account as a member of an existing
SQL Server database role in the current database. Permissions default to members
of the sysadmin server role and the db_owner database role to add
members to fixed database roles or user-defined roles. Owners of user-defined
roles can add members to the roles they own and members of the db_securityadmin
database role can add users to any user-defined role. |
sp_droprolemember
|
System stored procedure that removes a security account from a SQL Server
role in the current database. Permissions default to members of the sysadmin
server role and the db_owner and db_securityadmin database and are
not transferable and only a member of the sysadmin fixed server role or the db_owner
fixed database role can remove users from a fixed database role. |
sp_helprole
|
System stored procedure that returns information about the roles in the current
database.
|
sp_helpuser
|
System stored procedure that reports information about SQL Server users,
Windows NT users, and database roles in the current database. |
| IS_MEMBER
|
Security function that returns an integer indicating whether the current
user is a member of the specified NT group or SQL Server role. |
sp_addapprole
|
System stored procedure that adds a special type of role in the current database
used for application security. Permissions default to members of the sysadmin
server role, and the db_owner and db_securityadmin database roles |
sp_dropapprole
|
System stored procedure that removes an application role from the current
database. Permissions default to members of the sysadmin server role, the
db_securityadmin and db_owner database roles and are not transferable. |
sp_approlepassword
|
System stored procedure that changes the password of an application role
in the current database. Permissions default to members of the sysadmin
server role, and the db_securityadmin and db_owner database roles
and are not transferable. |
sp_setapprole
|
System stored procedure that activates the permissions associated with an
application role in the current database. |
By Randy
Dyess
