Technical Analysis and Practical Methods for Determining Object Creators in SQL Server 2005

Technical Background and Problem Analysis

In SQL Server database management, particularly in development environments, there is often a need to clean up old test objects such as stored procedures and tables. A common question arises: Is it possible to determine who created these objects? The answer is not straightforward and requires a deep understanding of SQL Server's metadata storage mechanisms.

Core System View Analysis

SQL Server 2005 stores object metadata in the system view sys.objects, where the principal_id field theoretically identifies the object owner. This field is a foreign key that links to the sys.database_principals view, which in turn can be associated with SQL Server instance login information.

However, a critical limitation exists: principal_id is only non-NULL when the object owner differs from the schema owner. In typical development environments, since users often have dbo privileges, this field is frequently NULL, making it difficult to track creators through this method.

-- Query objects and their principal_id
SELECT 
    name AS object_name,
    principal_id,
    type_desc
FROM 
    sys.objects
WHERE 
    principal_id IS NOT NULL;

Impact of Permission Architecture

When using NT authentication or SQL authentication, if all users share the same elevated privileges (e.g., dbo), the system does not individually record creators for each object. Additionally, when employees leave and domain logins are deleted, related metadata may be lost or incomplete, further complicating tracking efforts.

Supplementary Technical Methods

Although the principal_id field in sys.objects is often NULL, other approaches can be attempted:

1. Default Trace Reports: The "Schema Changes History" report in SQL Server Management Studio provides a history of committed DDL statements, including the executing login name. This may be effective for recently created objects.

2. Traditional System Table Queries: For SQL Server 2005, one can query the sysobjects and sysusers system tables:

SELECT 
    so.name AS object_name,
    su.name AS user_name,
    so.crdate AS creation_date
FROM 
    sysobjects so 
JOIN 
    sysusers su ON so.uid = su.uid  
ORDER BY 
    so.crdate;

However, this method relies on uid mapping and may be inaccurate in complex permission environments.

Practical Recommendations and Conclusion

In the absence of dedicated auditing setups, determining object creators primarily depends on system metadata, and the limitations of the principal_id field pose challenges. Recommendations include:

• Establish clear naming conventions and auditing mechanisms early in development
• Regularly review system views, even if principal_id is NULL, and analyze other metadata such as creation dates
• For critical environments, consider enabling SQL Server audit features or third-party monitoring tools

In summary, while it may not be technically feasible to definitively identify every object's creator, a combination of system views and trace reports can support database cleanup and maintenance efforts to some extent.