Database Role Membership

There are two kinds of database roles in SQL Server: fixed roles and flexible roles. In this article I am going explain about fixed roles.

Best and Cheap SQL Server 2014 Cloud Hosting - Database Role Membership

Fixed roles automatically exist in each database. Adding a user to one of these roles will not change that user’s permissions in any other database. Any user or role can be added to a database role. Once a user has been added to a role, they can add other users or roles to that role.

Let’s take a look at the fixed roles available in SQL Server:

Best and Cheap SQL Server 2014 Cloud Hosting - Database Role Membership

db_owner

A member of the db_owner role can do anything inside the database. They can grant and revoke access, create tables, stored procedures, views, run backups and schedule jobs.

db_securityadmin

Members of the db_securityadmin role can modify role permissions and manage permissions. Users in this role have, in theory, almost as much power members of db_owner. The only thing that a member of db_securityadmin can’t do is add users to the db_owner role. Members of db_securityadmin also cannot add users to fixed database roles (this requires membership in the db_owner role).

db_accessadmin

Members of the db_accessadmin role have the ability to change database access. They can grant and revoke access to Windows logins, Windows groups and SQL Server logins. The users that they grant access to will be members of the Public role and will have all the privileges associated with that role.

Best and Cheap SQL Server 2014 Cloud Hosting - Database Role Membershipdb_backupoperator

Members of the db_backupoperator fixed database role can back up the database.

db_ddladmin

Members of the db_ddladmin fixed database role can run any Data Definition Language (DDL) command in a database.

db_datawriter

Members of the db_datawriter fixed database role can add, delete, or change data in all user tables.

db_datareader

Members of the db_datareader role are able to read all data from all user tables. Even the super secret table can be read by members of db_datareader. So the db_datareader role allows a user to be able to issue a SELECT statement against all tables and views in the database.

db_denydatawriter

Members of the db_denydatawriter fixed database role cannot add, modify, or delete any data in the user tables within a database.

db_denydatareader

Members of the db_denydatareader fixed database role cannot read any data in the user tables within a database.

Public

Every SQL Server login belongs to the public server role. When a server principal has not been granted or denied specific permissions on a securable object, the user inherits the permissions granted to public on that object. Only assign public permissions on any object when you want the object to be available to all users. You cannot change membership in public.

Note: 

Public is implemented differently than other roles. However, permissions can be granted, denied, or revoked from public.

Best and Cheap SQL Server 2014 Cloud Hosting

Are you looking for best and cheap SQL Server 2014 cloud hosting? ASPHostPortal.com is the best choice for you. They allow you full remote connectivity to your SQL Server 2014 Hosting database and do not restrict access in any way. With their SQL Server hosting package, there’s no need to rebuild your database from scratch should you wish to transfer an existing SQL Server database to us. If you already have a database hosted elsewhere, you can easily transfer the contents of your database using SQL Server Management Studio which is fully supported by their packages. SSMS provides you with an Import/Export wizard whom you can use to upload your data and stored procedures with a couple of clicks. For more information please visit ASPHostPortal.com official site at http://asphostportal.com or please contact them by email at [email protected]
Rate this post