Manage database security roles
Principals are granted access to resources through a role-based access control model, where their assigned security roles determine their resource access.
In this article, you’ll learn how to use management commands to view existing security roles and add and drop principal association to security roles on the database level.
Permissions
You must have at least Database Admin permissions to run these commands.
Database level security roles
The following table shows the possible security roles on the database level and describes the permissions granted for each role.
| Role | Permissions |
|---|---|
admins | View and modify the database and database entities. |
users | View the database and create new database entities. |
viewers | View tables in the database where RestrictedViewAccess isn’t turned on. |
unrestrictedviewers | View the tables in the database even where RestrictedViewAccess is turned on. The principal must also have admins, viewers, or users permissions. |
ingestors | Ingest data to the database without access to query. |
monitors | View database metadata such as schemas, operations, and permissions. |
Show existing security roles
Before you add or remove principals, you can use the .show command to see a table with all of the principals and roles that are already set on the database.
Syntax
To show all roles:
.show database DatabaseName principals
To show your roles:
.show database DatabaseName principal roles
Parameters
| Name | Type | Required | Description |
|---|---|---|---|
| DatabaseName | string | ✔️ | The name of the database for which to list principals. |
Example
The following command lists all security principals that have access to the Samples database.
.show database Samples principals
Example output
| Role | PrincipalType | PrincipalDisplayName | PrincipalObjectId | PrincipalFQN |
|---|---|---|---|---|
| Database Samples Admin | Microsoft Entra user | Abbi Atkins | cd709aed-a26c-e3953dec735e | aaduser=abbiatkins@fabrikam.com |
Add and drop principal association to security roles
This section provides syntax, parameters, and examples for adding and removing principals to and from security roles.
Syntax
Action database DatabaseName Role ( Principal [, Principal…] ) [skip-results] [ Description ]
Parameters
| Name | Type | Required | Description |
|---|---|---|---|
| Action | string | ✔️ | The command .add, .drop, or .set..add adds the specified principals, .drop removes the specified principals, and .set adds the specified principals and removes all previous ones. |
| DatabaseName | string | ✔️ | The name of the database for which to add principals. |
| Role | string | ✔️ | The role to assign to the principal. For databases, roles can be admins, users, viewers, unrestrictedviewers, ingestors, or monitors. |
| Principal | string | ✔️ | One or more principals or managed identities. To reference managed identities, use the “App” format using the managed identity object ID or managed identity client (application) ID. For guidance on how to specify these principals, see Referencing Microsoft Entra principals and groups. |
skip-results | string | If provided, the command won’t return the updated list of database principals. | |
| Description | string | Text to describe the change that displays when using the .show command. |
| Name | Type | Required | Description |
|---|---|---|---|
| Action | string | ✔️ | The command .add, .drop, or .set..add adds the specified principals, .drop removes the specified principals, and .set adds the specified principals and removes all previous ones. |
| DatabaseName | string | ✔️ | The name of the database for which to add principals. |
| Role | string | ✔️ | The role to assign to the principal. For databases, this can be admins, users, viewers, unrestrictedviewers, ingestors, or monitors. |
| Principal | string | ✔️ | One or more principals. For guidance on how to specify these principals, see Referencing Microsoft Entra principals and groups. |
skip-results | string | If provided, the command won’t return the updated list of database principals. | |
| Description | string | Text to describe the change that displays when using the .show command. |
Examples
In the following examples, you’ll see how to add security roles, remove security roles, and add and remove security roles in the same command.
Add security roles with .add
The following example adds a principal to the users role on the Samples database.
.add database Samples users ('aaduser=imikeoein@fabrikam.com')
The following example adds an application to the viewers role on the Samples database.
.add database Samples viewers ('aadapp=4c7e82bd-6adb-46c3-b413-fdd44834c69b;fabrikam.com')
Remove security roles with .drop
The following example removes all principals in the group from the admins role on the Samples database.
.drop database Samples admins ('aadGroup=SomeGroupEmail@fabrikam.com')
Add new security roles and remove the old with .set
The following example removes existing viewers and adds the provided principals as viewers on the Samples database.
.set database Samples viewers ('aaduser=imikeoein@fabrikam.com', 'aaduser=abbiatkins@fabrikam.com')
Remove all security roles with .set
The following command removes all existing viewers on the Samples database.
.set database Samples viewers none
Related content
Feedback
Was this page helpful?
Glad to hear it! Please tell us how we can improve.
Sorry to hear that. Please tell us how we can improve.