none
How Can I know who has changed anything in SQL? RRS feed

  • Question

  • Hi All;

    Iam wondering if it's possible to know who has done any changes in SQL. For example; add view, delete table, modify table, create database, add column, stopped SQL agent job, create new job, modify rules, add roles, new login, and etc.

    would it be possible to get the SQL user who has done it? Also is it possible to know the domain user incase more then 1 person using te same SQL user?? I tried to search in the log and other folders but couldn't get anything. 

    Looking forward to hear form you .. Thanks in Advance :)

    Wednesday, November 23, 2011 9:06 AM

Answers

  • SQL Server only shows you the IP address of the Terminal Server machine; it has no knowledge about how the user connected to that server. You would have to find the RDP logs and enter them into the database somehow to correlate the information. Which still can be inconclusive if there are more than one user connected through RDP at the same time.

    I think you really need to find away to get rid of all usage of sa. What you have today is simply not good.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marked as answer by BuRaiR Tuesday, December 13, 2011 7:07 AM
    Monday, December 12, 2011 10:55 PM

All replies

  • DDL trigger on the database level

    http://www.simple-talk.com/sql/sql-server-2005/sql-server-2005-ddl-trigger-workbench/


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Wednesday, November 23, 2011 9:09 AM
  • DDL trigger on the database level

    http://www.simple-talk.com/sql/sql-server-2005/sql-server-2005-ddl-trigger-workbench/


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

    Hi Uri Dimant;

    Can you actually open the link ?! It's giving me an error 404 Page not found. Can you check from your side .. thanks. 
    Wednesday, November 23, 2011 9:23 AM
  • Wednesday, November 23, 2011 9:27 AM
  • Wednesday, November 23, 2011 9:29 AM
  • Yes http://www.simple-talk.com/sql/t-sql-programming/sql-server-2005-ddl-trigger-workbench/
    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Wednesday, November 23, 2011 9:44 AM
  • Hi again Uri & Anna;

    Okay the example given in the article is very helpful. Thanks. But I will need to create the table in the Database level. What if i have like 20 DB in the SQL?? Do I have to create the log in each DB or is it possible to add it to a system table , for example MSDB or master and it will monitor any changes in all the DB's?

    Will be waiting for your answers, and excuse me if I didnt get back to you in the coming few days, as we will be on leave. But will try to.

    Thanks again :))

    Thursday, November 24, 2011 10:54 AM
  • Deleted
    Friday, November 25, 2011 2:58 AM
  • Hi BuRaiR,
    As W.Shawn Melton said, we can enable and disable the default trace in SQL Server. Meanwhile we can use the default trace enabled option to enable or disable the default trace log files.

    According to your description, you want to capture the information about who changed the objects in SQL Server. We can use Default Trace to capture as Ana Mihalj mentioned. Please try to run the script, which works for me as below:

    DECLARE @filename VARCHAR(255) 
    SELECT @FileName = SUBSTRING(path, 0, LEN(path)-CHARINDEX('\', REVERSE(path))+1) + '\Log.trc'  
    FROM sys.traces   
    WHERE is_default = 1;  
    
    --Check who dropped and created objects, altered objects
    SELECT gt.HostName, 
           gt.ApplicationName, 
           gt.NTUserName, 
           gt.NTDomainName, 
           gt.LoginName, 
           gt.SPID, 
           gt.EventClass, 
           gt.IntegerData, 
           te.Name AS EventName,
           gt.EventSubClass,      
           gt.TEXTData, 
           gt.StartTime, 
           gt.EndTime, 
           gt.ObjectName, 
           gt.DatabaseName, 
           gt.FileName, 
    	   gt.IsSystem
    FROM [fn_trace_gettable](@filename, DEFAULT) gt 
    JOIN sys.trace_events te ON gt.EventClass = te.trace_event_id 
    ORDER BY StartTime;




    Regards, Amber zhang
    • Marked as answer by amber zhangModerator Wednesday, November 30, 2011 1:37 AM
    • Unmarked as answer by BuRaiR Wednesday, November 30, 2011 9:07 AM
    Friday, November 25, 2011 8:13 AM
    Moderator
  • Thanks All for your replies .. Will try the solutions and will get back to you within few days.

    Thanks Again.

    Wednesday, November 30, 2011 9:08 AM
  • No, create one table  in some database (I usually have Utility database) and DDL trigger will insert all the info over there. Then you can filter out by db name....
    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Wednesday, November 30, 2011 9:57 AM
  • DECLARE @filename VARCHAR(255)
    SELECT @FileName = SUBSTRING(path, 0, LEN(path)-CHARINDEX('\', REVERSE(path))+1) + '\Log.trc' 
    FROM sys.traces  
    WHERE is_default = 1; 


    Hi Amber Zhang,

    Thanks for your reply. I have tried to the first part you have given me but it's giving me an error. Iam running the script under the master database (Is that Okay?)?? Here is the Error:

    Msg 137, Level 15, State 1, Line 2

    Must declare the scalar variable "@FileName".

    What Shall I do? Sorry but not really familiar with the scripts. Hope to hear form you soon. Thanks.  

    Wednesday, November 30, 2011 11:00 AM
  • It seems that you are running this on a server with a case-sensitive collation. Just spell it @filename consistently, and you should be fine.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Wednesday, November 30, 2011 10:54 PM
  • It seems that you are running this on a server with a case-sensitive collation. Just spell it @filename consistently, and you should be fine.

    Thanks Erland .. Yup it's case sensetive.
    Sunday, December 4, 2011 6:06 AM
  • Hi All ,,,

    I have tried Amber Zhang script and it works with me BUT in the domain name field , it does not show me all the domain users who are accessing the Database server.

    For example; I have run the script again today and it shows me NTuserName and DomainName as NULL and LoginName as "sa".  I loged in to the server using my domain name, example: ABC_COM\999 then using "sa" to log in to the SQL. Iam wondering Why it does not shows me the domain name and the user who accessed the server?

    Is it possible to get this information? Because this is what iam looking for?!



    • Edited by BuRaiR Sunday, December 4, 2011 7:58 AM
    Sunday, December 4, 2011 6:14 AM
  • SQL Server supports two means of authentication: Windows authentication and SQL authentication. With Windows authentication, SQL Servers uses the information from Windows. With SQL authentication, SQL Server performs the authentication itself, and does not track whatever the Windows user may be. And this is not strange, because as far as SQL Server is concerned there does not have to be any Windows user. Someone knocks on the TCP port, presents his credentials and is let in. But that connection could come from a Unix box, or somewhere far beyond your domain.

    Now, if you always want to know the Windows user, there is still a cure: turn of mixed authentication, and only permit Windows authentication (which you cannot turn off). If you need to keep SQL Server authentication, at least disable generic accounts like sa, and make sure that everyone who connects to SQL Server uses his own personal account.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Sunday, December 4, 2011 10:34 AM
  • Now, if you always want to know the Windows user, there is still a cure: turn of mixed authentication, and only permit Windows authentication (which you cannot turn off). If you need to keep SQL Server authentication, at least disable generic accounts like sa, and make sure that everyone who connects to SQL Server uses his own personal account.

    Hi Erland;

    Thanks for your reply. Well turnning off the mixed authentication and disable the "sa" user it will be difficult for us. All the SQL agent jobs, SSIS jobs using "sa" password and this "sa" password for the SQL is held by few people as it was a "test server" at the begging and suddenly turned to be as an important server.

    The replies i got earlier was good, the only thing is missing to know who accessed the databse server. If i can find way for this, then it will solve my problem.

    Thanks again Erland.

    Thursday, December 8, 2011 10:53 AM
  • And the bad news is that if you want to know the actual person who performed all actions, you will need to have to do the hard work and stamp out all use of sa and all other sorts of anonymous accounts.

    If you good auditing, you must require that people use their own personal accounts for all access.

    In the meanwhile, I would think that your best bet is to get the IP address from sys.dm_exec_connections. But if people access the server by logging in through remote desktop and then to SQL Server as sa, there is nothing you can find out from SQL Server about the person. (Windows may have information about the RDP login.)


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Thursday, December 8, 2011 10:48 PM
  • But if people access the server by logging in through remote desktop and then to SQL Server as sa, there is nothing you can find out from SQL Server about the person


    Hi Again Erland,

    Thanks for giving me some of your valubale time. Well according to your reply above, seems there is no way to get who have access the server from remore logging. This is exactly how they access the server, via remote logging using their domain account, then choose "sa" for SQL.

    You have mentioned something about RDP? What do u mean by that? something like "Event Viewer" ?? Is it possbile to output a file contains both info from SQL and Window if the RDP shows who accessed the server?

    Sunday, December 11, 2011 6:35 AM
  • RDP = Remote Desktop.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Sunday, December 11, 2011 10:53 AM
  • RDP = Remote Desktop.

     Thanks again Erland :))

    I was thinking about using the first script and join the IP Adress column (If it shows the RDP IP source), in one output. Will try to work on it, not really sure if it will work but will need to try it.

    Thanksss dude.

    Monday, December 12, 2011 11:05 AM
  • SQL Server only shows you the IP address of the Terminal Server machine; it has no knowledge about how the user connected to that server. You would have to find the RDP logs and enter them into the database somehow to correlate the information. Which still can be inconclusive if there are more than one user connected through RDP at the same time.

    I think you really need to find away to get rid of all usage of sa. What you have today is simply not good.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marked as answer by BuRaiR Tuesday, December 13, 2011 7:07 AM
    Monday, December 12, 2011 10:55 PM
  •  think you really need to find away to get rid of all usage of sa. What you have today is simply not good.

    Hi Again Erland,

    Thanks Alot for helping me out with this. Well it seems complicated to do it in that way. Will need to find another way to get rid of "sa" usage as you said.

    Thanks Alot.

    Tuesday, December 13, 2011 7:07 AM