Snippet Name: Querying_Application_Roles

Tags:

Created Date: 2025-06-16 13:12:47
Last Modified Date: 2025-05-31 10:32:06

Dependencies:

DBA Notes:

Sample Output:

Title: Querying Application Roles
Author: user
in sql server, how can I query a list off application roles create via an example like CREATE APPLICATION ROLE nls_application_role

Author: ChatGPT
In SQL Server, you can retrieve a list of **Application Roles** created using `CREATE APPLICATION ROLE` by querying the `sys.database_principals` system view.

### Query:
```sql
SELECT name, principal_id, create_date, modify_date
FROM sys.database_principals
WHERE type = 'A';  -- 'A' represents application roles
```

### Explanation:
- `sys.database_principals` contains information about database-level security principals.
- `type = 'A'` filters for **Application Roles** specifically.
- The query returns:
  - `name`: The application role name.
  - `principal_id`: The ID assigned to the role.
  - `create_date`: When the role was created.
  - `modify_date`: Last modified date.

#### Example Output:
| name                   | principal_id | create_date          | modify_date         |
|------------------------|-------------|----------------------|---------------------|
| nls_application_role   | 265         | 2025-03-07 10:00:00  | 2025-03-07 10:00:00 |

Let me know if you need additional details or want to filter by specific application roles!