Audit : How to track logins getting dropped in SQL Server?
Tricky!! This is what I can say when I started troubleshooting the issue when suddenly the logins were getting dropped and no one had a clue what's going on since no one from the DBA team would go ahead and drop the logins. However, SQL 2008 and above have this beautiful feature called SQL Audit. Have a look on the below and I'm sure you would like the post :).
Below steps are going to help in tracking the issue ( windows logins getting dropped) :
1. If the issue is happening very often then collect the default trace.
2. Start SQL Audit to capture the information related to what's running and causing the logins to be dropped.
3. Use the Audit DMV's to find the correct "Audit action type" for usage. This is tricky as we don't have much documentation about what to select from the drop down when we click to start a new audit. ( I'm not talking about how to create an audit as there is ample documentation about the same).
4. To find the audit action type, I used the following and it was bang on ( Thanks to DMV's) :
select * from sys.dm_audit_class_type_map
select * from Sys.dm_audit_actions where name like'%drop%'andclass_desc like'%login%'
5. Now as I was looking for login getting dropped, I was able to narrow down to SERVER_PRINCIPAL_CHANGE_GROUP.
6. Similarly, we can track for the Schema alteration as well
select * from sys.dm_audit_actions where name='alter' and class_desc='object'
6. Once the audit is configured , any changes which happens on the server in relation to the logins it would be captured along-with the user id and the command which is being run :
7. Error log would have an entry like the below :
2013-10-15 05:30:34.200 Logon Error: 18456, Severity: 14, State: 5.
2013-10-15 05:30:34.200 Logon Login failed for user xxx\xxxx'. Reason: Could not find a login matching the name provided. [CLIENT: 10.xxx.xx..xxx]
8. Once you have captured the logs and have shared the finding with the engineering and they want to check how are the SP's getting called and they know the SP's then we can configure a profiler like below :
Disclaimer: I work at Microsoft. Everything here, is my personal opinion and is not read or approved by Microsoft before it is posted. No warranties or other guarantees will be offered as to the quality of the opinions or anything else offered here.