August 6, 2020 at 4:44 pm
I had the requirement to put on auditing. I made the server specifications, put in a file, and read the file info a database only the security officer can read. But now he sees too much. Every action in ssms is put in the database. The activity monitor is one of the worst. Can I put off soms events, without missing important info, or do I have to filter the lines from file? Or something else?
August 6, 2020 at 6:03 pm
I had the requirement to put on auditing. I made the server specifications, put in a file, and read the file info a database only the security officer can read. But now he sees too much. Every action in ssms is put in the database. The activity monitor is one of the worst. Can I put off soms events, without missing important info, or do I have to filter the lines from file? Or something else?
There are several different methods for auditing. Which one did you implement?
--Jeff Moden
Change is inevitable... Change for the better is not.
August 6, 2020 at 6:09 pm
I think this depends on what tool you are using to generate the data and what tool you are using to pull it into the database and what data you are hoping to capture for the security officer.
I do not know what you consider "important info", so I don't know what filtering method is best. You may be able to get by making a view on the table that filters out some of the data and let the security officer read that. This way you have all of the audit data, but they get a subset of the data that you want them to see.
It really depends on what you are trying to capture. You say you don't want to capture every action in SSMS, but I imagine you want to capture some?
If you are JUST looking for schema level changes and not data level changes and lookup queries, there are tools that can do this for you, and some are even free. But I don't know what to recommend to you if I don't know what you are needing to capture.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
August 7, 2020 at 8:20 am
The data in the database is sensitive. So it's not allowed to query it by persons who are not qualified. So if a login can start a query in SSMS using "New Query" I want to see it. I don't want the query's Activity Monitor is doing when you start it. I listed the the server audit specification below. Yesterday I started putting the standard query's from SSMS in a table and filter the records out (with a left outer join) . But there are a lot of statements. And do these change in a next version? Is there a simpler way?
CREATE SERVER AUDIT SPECIFICATION [ServerAuditSpecification_SQL02-PRD-ASD]
FOR SERVER AUDIT [Audit-sql02-aud01]
ADD (SCHEMA_OBJECT_ACCESS_GROUP),
ADD (DATABASE_ROLE_MEMBER_CHANGE_GROUP),
ADD (SERVER_ROLE_MEMBER_CHANGE_GROUP),
ADD (BACKUP_RESTORE_GROUP),
ADD (AUDIT_CHANGE_GROUP),
ADD (FAILED_DATABASE_AUTHENTICATION_GROUP),
ADD (DATABASE_LOGOUT_GROUP),
ADD (SUCCESSFUL_DATABASE_AUTHENTICATION_GROUP),
ADD (DBCC_GROUP),
ADD (DATABASE_PERMISSION_CHANGE_GROUP),
ADD (DATABASE_OBJECT_PERMISSION_CHANGE_GROUP),
ADD (SCHEMA_OBJECT_PERMISSION_CHANGE_GROUP),
ADD (SERVER_OBJECT_PERMISSION_CHANGE_GROUP),
ADD (SERVER_PERMISSION_CHANGE_GROUP),
ADD (DATABASE_PRINCIPAL_IMPERSONATION_GROUP),
ADD (SERVER_PRINCIPAL_IMPERSONATION_GROUP),
ADD (BROKER_LOGIN_GROUP),
ADD (FAILED_LOGIN_GROUP),
ADD (DATABASE_MIRRORING_LOGIN_GROUP),
ADD (SUCCESSFUL_LOGIN_GROUP),
ADD (LOGOUT_GROUP),
ADD (DATABASE_CHANGE_GROUP),
ADD (DATABASE_OBJECT_CHANGE_GROUP),
ADD (DATABASE_PRINCIPAL_CHANGE_GROUP),
ADD (SCHEMA_OBJECT_CHANGE_GROUP),
ADD (SERVER_OBJECT_CHANGE_GROUP),
ADD (SERVER_PRINCIPAL_CHANGE_GROUP),
ADD (SERVER_OPERATION_GROUP),
ADD (APPLICATION_ROLE_CHANGE_PASSWORD_GROUP),
ADD (LOGIN_CHANGE_PASSWORD_GROUP),
ADD (SERVER_STATE_CHANGE_GROUP),
ADD (DATABASE_OWNERSHIP_CHANGE_GROUP),
ADD (DATABASE_OBJECT_OWNERSHIP_CHANGE_GROUP),
ADD (SCHEMA_OBJECT_OWNERSHIP_CHANGE_GROUP),
ADD (SERVER_OBJECT_OWNERSHIP_CHANGE_GROUP),
ADD (TRACE_CHANGE_GROUP),
ADD (TRANSACTION_GROUP),
ADD (USER_CHANGE_PASSWORD_GROUP),
ADD (USER_DEFINED_AUDIT_GROUP)
WITH (STATE = ON)
GO
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply