Tuesday 28 May 2013

Find table name from Column Name

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

Example:



select so.name

from sysobjects so inner join syscolumns sc
ON so.id = sc.id where sc.name like '%supplier%'

Output like:

Find table name from Column Name

















above is list of Table that contain a column like Supplier.






Wednesday 8 May 2013

How to track failed login attempts in SQL Server


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.


USE MASTER

GO

CREATE SERVER AUDIT audit_test                                           

TO FILE (FILEPATH = 'C:\Audit')

GO

Now create audit specification against FAILED_LOGIN_GROUP.

CREATE SERVER AUDIT SPECIFICATION audit_test_spec       

FOR SERVER AUDIT [audit_test]

ADD (FAILED_LOGIN_GROUP)

WITH (STATE=ON)

GO

You can see these audits in Object Explorer under Security=>Audits.


 failed login attempts in SQL Server



Now suppose you log on to same instance on that above audit is applied with wrong credentials.

 failed login attempts in SQL Server


Now connect with right credentials. And go to
Object Explorer=> Security=>Audits=> audit_test
Right click on audit_test  and click View Audit Logs



 failed login attempts in SQL Server






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.