Permissions of the Standard Roles

The following standard server permissions are available in every SQL Server:

Fixed server-level role	Database-level permission	Server-level permission
db_accessadmin	ALTER ANY USER, CREATE SCHEMA Permission with GRANT option: CONNECT	VIEW ANY DATABASE
db_backupoperator	BACKUP DATABASE, BACKUP LOG, CHECKPOINT	VIEW ANY DATABASE
db_datareader	SELECT	VIEW ANY DATABASE
db_datawriter	DELETE, INSERT, UPDATE	VIEW ANY DATABASE
db_ddladmin	ALTER ANY ASSEMBLY, ALTER ANY ASYMMETRIC KEY, ALTER ANY CERTIFICATE, ALTER ANY CONTRACT, ALTER ANY DATABASE DDL TRIGGER, ALTER ANY DATABASE EVENT, NOTIFICATION, ALTER ANY DATASPACE, ALTER ANY FULLTEXT CATALOG, ALTER ANY MESSAGE TYPE, ALTER ANY REMOTE SERVICE BINDING, ALTER ANY ROUTE, ALTER ANY SCHEMA, ALTER ANY SERVICE, ALTER ANY SYMMETRIC KEY, CHECKPOINT, CREATE AGGREGATE, CREATE DEFAULT, CREATE FUNCTION, CREATE PROCEDURE, CREATE QUEUE, CREATE RULE, CREATE SYNONYM, CREATE TABLE, CREATE TYPE, CREATE VIEW, CREATE XML SCHEMA COLLECTION, REFERENCES	VIEW ANY DATABASE
db_denydatareader	No permission: SELECT	VIEW ANY DATABASE
db_denydatawriter	No permission: DELETE, INSERT, UPDATE	VIEW ANY DATABASE
db_owner	Permission with GRANT option: CONTROL	VIEW ANY DATABASE
db_securityadmin	ALTER ANY APPLICATION ROLE, ALTER ANY ROLE, CREATE SCHEMA, VIEW DEFINITION	VIEW ANY DATABASE
dbm_monitor	VIEW of the latest status in the Database Mirroring Monitor Important: The fixed database role dbm_monitor is created in the msdb database when the first database is registered in the Database Mirroring Monitor. The new dbm_monitor role has no members until a system administrator assigns users to the role.	VIEW ANY DATABASE

Fixed database roles are not equivalent to their database-level permission.

For example, the fixed database role db_owner has the CONTROL DATABASE permission.

However, granting the CONTROL DATABASE permission does not make a user a member of the fixed database role db_owner.

Members of the fixed database role db_owner are identified in the databases as the user dbo, whereas users with the CONTROL DATABASE permission are not.

Unless the built-in default user sa of Microsoft SQL Server is to be used for Q-Matrix™, the special user for Q-Matrix™ must be assigned the db_owner permission.

This applies only if the settings are made via Microsoft SQL Management Studio, since the special rights that are required cannot be set via the graphical interface.

With this setting, Q-Matrix™ can be used in normal operational mode, i.e. successful logins to Q-Matrix™ allow managing the data in the database on the SQL server.

Special cases:

Creating the initial Q-Matrix™ database
For the initial database to be created, the user must in this case have the db_owner permission.
Applying updates in the Q-Matrix™ database
For updates to be applied to the database, the user must in this case be assigned the db_owner permission.

In addition to these standard permission settings, the user can also be granted special CONTROL rights.

To do this, the required rights must be assigned to the user via a Transact SQL (T-SQL) command.

The syntax to be used is as follows:

GRANT <permission> [ ,...n ]
    TO <database_principal> [ ,...n ] [ WITH GRANT OPTION ]
    [ AS <database_principal> ]
<permission>::=
permission | ALL [ PRIVILEGES ]
<database_principal> ::=
    Database_user
    | Database_role
    | Application_role
    | Database_user_mapped_to_Windows_User
    | Database_user_mapped_to_Windows_Group
    | Database_user_mapped_to_certificate
    | Database_user_mapped_to_asymmetric_key
    | Database_user_with_no_login