[Question] SQL Server 2019 Standard Security Audit ?

Question

User-60902076 posted

[Question] SQL Server 2019 Standard Security Audit ?

Q1: Could I get the SQL Server audit informations about SSMS login? who ? success? failure?
Use SQL Server account to login ? or user Windows account?

Q2: Could I get the audit informations for some specific tables ? (who, where, what ..)

Q3: Could I get the audit informations where "select * from tableXX" is executed to query the access information of all fields ? (who, where, what ..)

https://docs.microsoft.com/zh-tw/sql/relational-databases/performance/view-the-sql-server-error-log-sql-server-management-studio?view=sql-server-ver15
In the [Source] field, I saw that either Server or Backup or spid started,
What different about these three categories ?
Is Server local machine or another Web Server ?
Is spid the screen generated by the [New Query] button?

SQL Server Audit records
https://docs.microsoft.com/zh-tw/sql/relational-databases/security/auditing/sql-server-audit-records?view=sql-server-ver15
CREATE SERVER AUDIT (Transact-SQL)
https://docs.microsoft.com/zh-tw/sql/t-sql/statements/create-server-audit-transact-sql?view=sql-server-ver15
It can not seem to set who is the SSMS login? success? failure?

Thank you everyone ^_^

Answer 1

User1535942433 posted

Hi luicst,

Q1: Could I get the SQL Server audit informations about SSMS login?

I suggest you could follow this and use SQL Server to login:

1.In Object Explorer, expand the Security folder.

2.Expand the Audits folder.

3.Right-click the audit log that you want to view and select View Audit Logs. This opens the Log File Viewer -server_name dialog box. For more information, see Log File Viewer F1 Help.

4.When finished, click Close.

You could refer to this article.

Q2: Could I get the audit informations for some specific tables ? Q3: Could I get the audit informations where "select * from tableXX" is executed to query the access information of all fields ?

I suggest you could use sql trigger to fire  on a particular even and you could use a third tool:ApexSQL Trigger to select the table and column to audit or you could use sp_trace_setfilter.

More details,you could refer to below articles:

https://docs.microsoft.com/en-us/sql/t-sql/statements/create-trigger-transact-sql?view=sql-server-ver15

sp_trace_setfilter:

https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-trace-setfilter-transact-sql?view=sql-server-ver15

ApexSQL Trigger：

https://www.apexsql.com/sql-tools-trigger.aspx

Best regards,

Yijing Sun