I have an issue where I have a user login which keeps being dropped randomly without any trace in the error log. How do I find out why it's behaving as such or who is doing this, even though I am sure it's not a person who is doing this. Thanks
Sql-server – SQL Server 2014 user login keeps being dropped
loginsSecuritysql server 2014
Related Solutions
For a failover-type situation, I use the handy 'sp_help_revlogin' stored procedure as published in http://support.microsoft.com/kb/918992
This procedure, when run on your primary server, will script out each login WITH the SID (this is the important part for making sure your logins are mapped to their respective database users) and hashed password (so the password remains the same on each server.) The resulting script can then be run against your failover server to create the logins. Be sure to review the output before running it.
As-is, it scripts all of the logins in the server, but could be modified to script only the logins you're interested in. It could also be automated to generate a script on a regular basis.
My article will help if you set it up in advance, but not when the event happened in the past and you didn't have any kind of auditing mechanism set up.
There is still hope, though. Let's say I did this:
CREATE LOGIN flooberella WITH PASSWORD = N'x', CHECK_POLICY = OFF;
This information is in the default trace under EventClass 104 (Audit Addlogin Event). However, if I change the password using either of these methods:
ALTER LOGIN flooberella WITH PASSWORD = N'y';
EXEC sp_password N'y', N'z', N'flooberella';
These events are not captured by the default trace, for obvious security reasons - it should not be possible for anyone with access to the default trace to figure out what someone else's password is, nor do they want to make it easy to even find out that a password has been changed (polling the frequency of these events, for example, can reveal certain properties of your security strategy).
So what else can you do? While this relies on the information still being in the log, and it also relies on using an undocumented DBCC command against a system database (you may wish to back up master and restore it elsewhere), you can get some information from the transaction log, e.g.:
DBCC LOG(master, 1);
This will yield, for the above two commands, rows with the following (partial) information:
Current LSN Description
====================== ======================================================================
000000f2:000001b8:0002 ALTER LOGIN;0x01050000000000051500000093a3bcd7a9f8fb1417ab13bce8030000
000000f2:000001b8:0004 Alter login change password;0x01050000000000 ... same sid as above ...
Doesn't seem like much, but now take that 0x portion of the description, and then do:
SELECT name FROM sys.server_principals
WHERE sid = 0x01050000000000051500000093a3bcd7a9f8fb1417ab13bce8030000;
Smoking gun! This is the person responsible for that event.
Of course, if they use ALTER LOGIN
syntax for all operations (which they should be using instead of sp_password
), you can't distinguish between someone changing the default database and someone changing the password. You also can't tell (at least that I can see) which login this affected, only that this person changed a login. Jon seems to think that this information is in the log as well, but I failed to find it (unlike the time information, which somehow I scrolled right past).
There may be different answers for contained users in SQL Server 2012 - though I suspect password changes are still obfuscated in similar ways. Will leave that for a separate question.
Best Answer
Possibly setup, sql audit, a server side trace/extended event and filter for sp_droplogin or a server-scoped trigger like below.
Then simply query ##AuditDroppedLogin for any events. Very basic example.
Below is also a server side trace script to track sp_droplogin
To stop before 48 hours run, replace 2 with your TraceID. EXEC sp_trace_setstatus 2, 0