When someone modifies or alters a stored procedure, the information is stored in logs. By accessing the logs you can know who modified the stored procedure and when.
How to find who modified a stored procedure in SQL Server
To find who modified a stored procedure in SQL Server, follow the below approaches.
- Approach-1: Accessing Schema Changes History in SQL Server Management Studio.
- Approach-2: Accessing the trace file in SQL Server Profiler
- Approach-3: Using T-SQL query
But before using one of these methods, you have to give the necessary permissions to the Log Files. To give the necessary permissions, follow the steps below:
- Navigate to the SQL Server installation directory.
- The path to the log files should look like:
C:\Program Files\Microsoft SQL Server\MSSQL15.SQLEXPRESS\MSSQL\Log- The Log folder stores the log files created by the SQL Server.
- The log file that you require will have the .trc extension. For example, log_24.trc.
- The log file having a greater number in its name will be the most recent log file. For example. if we have two log files named log_26.trc and log_27.trc, the log_27.trc is the recent one and will contain the lastest modifications.
- Right-click on the required log file, click on Properties, navigate to the Security tab, and click on Advance.
- Click on Enable Inheritance and click on OK to save the changes.
Read: Find Store Procedure in SQL Server by Table Name
Approach-1: Accessing Schema Changes History in SQL Server Management Studio
You can track the schema changes in SQL Server Management Studio. Follow the below steps to access the Schema Changes History:
- Open SQL Server Management Studio and navigate to the SQL Server Instance name in the Object Explorer Window.
- Right-click on the SQL Server Instance and click Reports > Standard Reports > Schema Changes History.
- Once you have accessed the Schema Changes History, you can find the procedure name and the information about its modification in the logs.
- In our case, we modified the addition stored procedure.
- You can check the Login Name column to determine who modified the stored procedure.
Read: Try catch in SQL Server stored procedure
Approach-2: Accessing the trace file in SQL Server Profiler
You can also access the log files using the SQL Server Profiler. We have installed the SQL Server Express Edition and the SQL Server Profiler was automatically installed with the SQL Server.
- To access the log files, navigate to the SQL Server installation directory in Windows Explorer.
- The path should be something like this:
C:\Program Files\Microsoft SQL Server\MSSQL15.SQLEXPRESS\MSSQL\Log- Open the desired log file with SQL Server Profiler.
- You can see the stored procedure name in the ObjectName column and the name of the user who modified the stored procedure name in the LoginName column.
Approach-3: Using T-SQL query
You can use also access the log files using the T-SQL queries. The advantage of this method is that you can access only the required information from the log. Also, you can use some specific information in your programming language. For example, create a stored procedure to access some specific information.
We can use the sys.fn_trace_gettable function to access the log files. For example, to access the Login Name and modification date, you can write the T-SQL query as:
SELECT ObjectName, StartTime, LoginName
FROM sys.fn_trace_gettable('C:\Program Files\Microsoft SQL Server\MSSQL15.SQLEXPRESS\MSSQL\Log\log_27.trc', default)
WHERE ObjectName= 'addition'
Note: Edit the Log File path in the above query.
Hence, you might have learned the various methods to find out who modified the stored procedure and when.
Conclusion
There are multiple ways to find who modified a stored procedure in SQL Server as explained in this article as mentioned here.
You may also like following the articles below.
- How To Get Table Row Count In SQL Server
- How To Check SQL Server Edition
- How To See What Version Of SQL Server Is Running
- How To Check Datatype Of Column In SQL Server
I am Bijay having more than 15 years of experience in the Software Industry. During this time, I have worked on MariaDB and used it in a lot of projects. Most of our readers are from the United States, Canada, United Kingdom, Australia, New Zealand, etc.
Want to learn MariaDB? Check out all the articles and tutorials that I wrote on MariaDB. Also, I am a Microsoft MVP.
