Is ‘VIEW DATABASE SECURITY AUDIT’ available for Dedicated SQL Pools?

Question

Is ‘VIEW DATABASE SECURITY AUDIT’ available for Dedicated SQL Pools?

Josue Gonzalez 110

Can you grant ‘VIEW DATABASE SECURITY AUDIT’ on Dedicated SQL Pool?

phemanth 15,315 Reputation points Microsoft External Staff

2023-10-03T11:21:25.0466667+00:00

@Josue Gonzalez

Thanks for approaching Microsoft Q&A

unfortunately, it is not possible to grant the 'VIEW DATABASE SECURITY AUDIT' permission on a Dedicated SQL Pool (formerly SQL DW)

. This permission is not available for Azure Synapse Analytics either.

. However, there are alternative ways to view audit logs. One option is to use the sys.fn_get_audit_file function, but this requires the CONTROL SERVER permission, which may not be desirable.

. Another option is to enable auditing on the SQL Pool and use Security Insights to view the audit logs.

. It is also possible to use Azure Defender for SQL to monitor and detect potential threat.

for any other queries please do let us know.

Accepted answer

0 additional answers

Your answer

phemanth 15,315 Reputation points Microsoft External Staff

2023-10-03T11:21:25.0466667+00:00

@Josue Gonzalez

Thanks for approaching Microsoft Q&A

unfortunately, it is not possible to grant the 'VIEW DATABASE SECURITY AUDIT' permission on a Dedicated SQL Pool (formerly SQL DW)

. This permission is not available for Azure Synapse Analytics either.

. However, there are alternative ways to view audit logs. One option is to use the sys.fn_get_audit_file function, but this requires the CONTROL SERVER permission, which may not be desirable.

. Another option is to enable auditing on the SQL Pool and use Security Insights to view the audit logs.

. It is also possible to use Azure Defender for SQL to monitor and detect potential threat.

for any other queries please do let us know.

Answer 1

Erland Sommarskog 120.2K MVP

Well, if you look at the topic for CREATE DATABASE AUDIT SPECIFICATION, you will find that this comment is only available on SQL Server and Azure Managed Instance. Synapse in any form is not listed. And if you can't have any database audit, there is not much point with having a permission for it.

Josue Gonzalez 110 Reputation points

2023-10-02T21:58:22.3133333+00:00

We are attempting to grant the ‘VIEW DATABASE SECURITY AUDIT’ permission to a domain service account on a Dedicated SQL Pool (formally SQL DW). The command completes successfully when run on regular Azure SQL databases.

The command being issued is:

GRANT VIEW DATABASE SECURITY AUDIT to [******@domainName.com];

The error we get is:

“Msg 104001, Level 16, State 1, Line 1 Incorrect syntax near 'VIEW'.”

My understanding is that Dedicated SQL Pools (formally SQL DW) are now part of Azure Synapse Analytics and according to the following page the command should be available for Azure Synapse:

https://learn.microsoft.com/en-us/sql/relational-databases/security/permissions-database-engine?view=azure-sqldw-latest

Is it possible to grant ‘VIEW DATABASE SECURITY AUDIT’ on our Dedicated SQL Pool and if so, how? Thanks!

Share via

Is ‘VIEW DATABASE SECURITY AUDIT’ available for Dedicated SQL Pools?

0 additional answers

Your answer