Determine What User Created Objects in SQL Server

sql

sql-server

database-audit

metadata

byAlex Kataev·Feb 4, 2025

To quickly identify who created SQL Server objects, join the sys.objects view, sys.schemas, and sys.database_principals. The principal_id column connects object owners to schemas. Review this SQL snippet:

SELECT o.name AS Object, dp.name AS Creator, o.create_date
FROM sys.objects o
JOIN sys.schemas s ON o.schema_id = s.schema_id
JOIN sys.database_principals dp ON s.principal_id = dp.principal_id
WHERE o.type IN ('U', 'P', 'V') -- Meanwhile in the land of Tables ('U'), Procedures ('P'), and Views ('V')...
ORDER BY o.create_date;

The above statement retrieves object name, creator, and created timestamp. Alter the WHERE clause to target your specific objects.

Deep Dive: Navigating the Object Creation Mystery

Schema Changes History report in SQL Server Management Studio presents a clear picture of all DDL statements, which aids in identifying the time and user associated with object creation, alteration, or deletion.

For scenarios where the principal_id might return NULL, this report reflects the login name tied to the DDL statement execution. Keep in mind, in case of shared logins, this might not accurately point at an individual.

For a thorough audit, especially when precision is key, exploring SQL Server traces or Extended Events could be beneficial. Apart from continuous monitoring, this feature records a detailed history of actions, mapping them back to the responsible login.

But remember, if users have dbo rights or are closely tied to the schema owner, principal_id may not accurately reflect the original creator.

Taking a Methodical Approach: Tips for Historical Unearthing

For times when principal_id information is missing, or when dealing with an older SQL Server 2005 version, a workaround might be to explore the sysobjects table, joined with sysusers:

SELECT o.name, o.crdate AS CreationDate, u.name AS Creator
FROM sysobjects o
INNER JOIN sysusers u ON o.uid = u.uid
ORDER BY o.crdate;

While this strategy can unravel chronological object creation, complete recoverability can't be promised. Metadata precision tends to "blur" with system upgrades and migrations.

Third-party tools like Adam Machanic's sp_whoisactive offer dynamic real-time insights into object changes - but remember, it doesn't retain a history.

Change Data Capture and SQL Server Audit, available in newer versions, offer a more granular trail of user activities, crucial for databases adhering to strict compliance and audits.

explain-codes / Sql / Determine What User Created Objects in SQL Server

Linked

How do I obtain a Query Execution Plan in SQL Server?



Difference between datetime and timestamp in SQL Server?



Sql Query for Logins



How to identify whether the table has an identity column



How to find which views are using a certain table in SQL Server (2008)?

