你当前正在访问 Microsoft Azure Global Edition 技术文档网站。 如果需要访问由世纪互联运营的 Microsoft Azure 中国技术文档网站，请访问 https://docs.azure.cn。

Manage database security roles

Applies to: ✅ Microsoft Fabric ✅ Azure Data Explorer

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.

Note

To delete a database, you need at least Contributor Azure Resource Manager (ARM) permissions. To assign ARM permissions, see Assign Azure roles using the Azure portal.

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.

Note

It isn't possible to assign the viewer role for only some tables in the database. For different approaches on how to grant a principal view access to a subset of tables, see manage table view access.

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

Learn more about syntax conventions.

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 ]

Learn more about syntax conventions.

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, 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.

Note

The .set command with none instead of a list of principals will remove all principals of the specified role.

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

• current_principal_details()