Search This Blog

Finding user Id's and details of objects like, tables, procedures or functions in SQL server


As an active user of various forums and groups someone asked below questions on my SQL/MSBI Developer & Jobs Group.  

Issues asked by Users

Requirement 1

Can someone please help me to trace the user Id who created the objects like, tables, procedures and functions etc.... in SQL server?

Requirement 2

Can anyone help me for the below requirement
1) Need to get Deleted Stored Procedure’s name
2) Need to get Deleted Table name and function With User name and Timing

Solution is simple and same for all two above Requirement.

Fetching all this type of Info like who has created the objects (Table, Function, SP and view) in which database and on which date in SQL is simple. Just follow the below steps.

1. Right click on Your Server (SQL Server)

2. Click on Reports.

3. Click on standard Reports.

4. Click on Schema Change History as below.    

                                        
5. Finally you will get the complete Report who has created what object with what name as below.
                                                                         
In the above Report you will see I have created one SP, one Function one table with different users and so on. with different user (Sa and Rakesh).