One of my friend ask to provide data as invoice_no,supplier_code etc. of Supplier.Now my database have more than 300 tables and it is difficult to find which table have these column names manually.So I find it by querying on joining of sysobjects and sys.columns
Tuesday, 28 May 2013
Wednesday, 8 May 2013
SQL Server Audit allows you to track and monitor Database/Server level events occurs on database engine.For LOGIN events there is a SQL Server audit action group as “FAILED_LOGIN_GROUP”.You can see list of specifications here.
For auditing login attempts you have first create Server Audit.
CREATE SERVER AUDIT audit_test
TO FILE (FILEPATH = 'C:\Audit')
Now create audit specification against FAILED_LOGIN_GROUP.
CREATE SERVER AUDIT SPECIFICATION audit_test_spec
FOR SERVER AUDIT [audit_test]
You can see these audits in Object Explorer under Security=>Audits.
Now suppose you log on to same instance on that above audit is applied with wrong credentials.
Now connect with right credentials. And go to
Object Explorer=> Security=>Audits=> audit_test
Right click on audit_test and click View Audit Logs
In logs you can see various details of Client who have tried to login Database engine.Also a log file has been created to defined destination.