0

We have a read-only login account that unfortunately was created without proper planning, and now users throughout the company are using this login to access tables and views in our database. The database has grown to include private information we'd rather keep outside of this read-only login. Changing it to access only certain tables is not an option, as it will break other macros and other ODBC related access that could require access to tables we've not thought of. The database has hundreds of tables!

Is there a way (auditing preferably), to record all tables or views the user has accessed? I'd rather use auditing than tracing, only due to the possible file size, but I am happy to be convinced on a better method.

Has to be for SQL Server 2012 R2.

Thanks

UPDATE

I've tried the SQL Audit methods described here, but I don't see options in SQL Server 2012 to audit SELECT for example.

I've created a new audit and enabled it.

Then I tried to create a new Server Audit Specification, but this is where I am stuck.

enter image description here

1

1 Answer 1

-1

Audit is built on SQL Server Extended Events so you can do the similar by creating an EE session, put a filter on your login and event type sql_statement_completed, with sql_text like '%SELECT%'.

https://learn.microsoft.com/en-us/sql/relational-databases/extended-events/quick-start-extended-events-in-sql-server?view=sql-server-ver16

1

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.