Introduction to SQL Server role membership and permissions to facilitate the administration of permissions in the database, SQL Server provides several roles, which are security principals that are used to group other principals. They are similar to groups in the microsoft windows operating system. Permission scopes for database-level roles are database-scoped. There are two types of database-level roles in SQL server : The predefined fixed database roles in the database and the flexible database roles that you can create. Fixed database roles are defined at the database level and exist in each database. Members of the db_owner and db_securityadmin database roles can manage fixed database role memberships. However, only members of the db_owner database role can add members to the db_owner fixed database role. There are also special purpose fixed database roles in the msdb database. You can add any database account and other SQL server roles to the database-level role. Each member of a fixed database role can add additional logins to the same role. Database-level role names and descriptions db_owner members of the fixed database role can perform all configuration and maintenance activities for the database, and you can also delete the database. Members of the db_securityadmin fixed database role can modify role memberships and administrative permissions. Adding a principal to this role may result in unexpected permission escalation. Members of the db_accessadmin fixed database role can add or remove database access permissions for Windows logins, Windows groups, and SQL server logins. Members of the db_backupoperator fixed database role can back up the database. Members of the db_ddladmin fixed database role can run any data definition language (DDL) commands in the database. Members of the db_datawriter fixed database role can add, delete, or change data in all user tables. Members of the db_datareader fixed database role can read all data from all user tables. Members of the db_denydatawriter fixed database role cannot add, modify, or delete any data from user tables in the database. Members of the db_denydatareader fixed database role cannot read any data from user tables in the database. about database-level fixed role permissionsFor specific information, see fixed database role permissions (Database engine).  MSDB role Name and description db_ssisadmin db_ssisoperator db_ssisltduser Members of these database roles can manage and use SSIS. An instance of SQL Server that was upgraded from an earlier version might contain an older version of the role that was named using Data transformation Services (DTS) instead of SSIS. For more information, see Using the Integration Services role. dc_admin dc_operator dc_proxy members of these database roles can manage and use the Data collector. For more information, see Security for data collectors. Members of the policyadministratorrole db_ policyadministratorrole database role can perform all configuration and maintenance activities on policy-based management policies and conditions. For more information, see Managing servers with policy-based management. ServerGroupAdministratorRole ServerGroupReaderRole members of these database roles can manage and use registered server groups. For more information, see Create a server group. IMPORTANT: Members of the db_ssisadmin role and dc_admin role may be able to elevate their permissions to the sysadmin. This elevation of privilege occurs because these roles can modify the integration Services package, and sql server can use the sysadmin security context of SQL Server agent to perform integration services package. To prevent this elevation of privilege from running maintenance plans, data collection sets, and other integration Services packages, configure the SQL server Agent job that runs the package to use a proxy account that has limited permissions, or add only the sysadmin member to the Db_ Ssisadmin and dc_admin roles. Using server-level role sp_helpdbfixedrole (Transact-SQL) &nbsP → Meta Data → Returns a list of fixed database roles.  SP_DBFIXEDROLEPERMISSION (Transact-SQL) → metadata → displays permissions for fixed database roles.  SP_HELPROLE (Transact-SQL) → metadata → returns information about roles in the current database.  SP_HELPROLEMEMBER (Transact-SQL) → metadata → returns information about the members of a role in the current database.  SYS.DATABASE_ROLE_MEMBERS (Transact-SQL) → metadata → returns a row for each member of each database role.  IS_MEMBER (Transact-SQL) → metadata → Indicates whether the current user is a specified Microsoft Windows group or microsoft SQL Serve Members of the R database role. create role (Transact-SQL) → commands → Create a new database role in the current database. alter role (Transact-SQL) → commands → Change the name of the database role. drop role (Transact-SQL) → commands remove roles from the database.  SP_ADDROLE (Transact-SQL) → commands → Create a new database role in the current database.  SP_DROPROLE (Transact-SQL) → commands → Remove database roles from the current database.  SP_ADDROLEMEMBER (Transact-SQL) → command → Adds a database user, database role, Windows logon name, or Windows group to the database role in the current database.  SP_DROPROLEMEMBER (Transact-SQL) → commands → Remove the security account from the SQL server role of the current database. public database roles Each database user belongs to the public database role. If a user is not granted or denied specific permissions to a securable object, the user inherits the permissions granted to the public role of the object.
Introduction to SQL Server role membership and permissions
