1 / 15

Auditing Database DDL Changes with SQLVer

Auditing Database DDL Changes with SQLVer. About PASS.

btrimble
Download Presentation

Auditing Database DDL Changes with SQLVer

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Auditing Database DDL Changes with SQLVer

  2. About PASS The PASS community encompasses everyone who uses the Microsoft SQL Server or Business Intelligence Platforms. This includes database administrators, application developers, Business Intelligence professionals, information technology professionals, executives, consultants, educators, instructors, and students. The Professional Association for SQL Server (PASS) is the only independent, user-run, not-for-profit association dedicated to the SQL Server community helping its members Connect, Share, and Learn by: • Facilitating member networking and exchange of information through our websites, Chapters, annual Summits, regional and local events, and virtual communities • Working with Microsoft to influence the evolution of SQL Server products and services • Delivering high quality, up-to-date, technical content, presentations, and other educational material for professional development

  3. DDL, DML? • Data Definition Language • Vocabulary used to define data structures in SQL Server • ALTER, CREATE, DROP, etc. • https://msdn.microsoft.com/en-us/library/ff848799.aspx • Data Manipulation Language • Vocabulary used to retrieve and work with data in SQL Server • Add, modify, query, or remove data from a SQL Server database. • INSERT, UPDATE, SELECT, DELETE, etc. • https://msdn.microsoft.com/en-us/library/ff848766.aspx • DML Trigger (Caution Will Robinson!)

  4. DDL Triggers • Fire in response to DDL Events • DDL Events • CREATE • ALTER • DROP • GRANT • DENY • REVOKE • UPDATE STATISTICS • SP_CONFIGURE

  5. DDL Triggers • Uses • Prevent changes in schema • Fire another event in response to schema change • Log changes or events in schema • Types • Transact-SQL DDL Trigger • Special T-SQL SPROC executes one or more TSQL • Database Scoped (e.g. DROP TABLE) • Server Scoped (e.g. ALTER SERVER CONFIGURATION, SP_CONFIGURE)

  6. DDL Triggers • Types (cont.) • CLR DDL Trigger • Executes one or more managed code statements (.NET Methods) • Other Important Stuff to Know • EVENTDATA function captures info (XML) • Only fire when associated DDL statements run • Cannot be used as INSTEAD OF triggers • Ignore DDL statements effecting local or global temporary tables and stored procedures (http://sqlblog.com/blogs/jamie_thomson/archive/2009/11/25/temporary-procedures-sql-server.aspx) • Template Explorer

  7. EventData Function • Captures info about event that fired the DDL trigger and subsequent changes • Returns XML - https://msdn.microsoft.com/en-us/library/hh213611.aspx • Time of Event • SPID • Event Type • Additional info depending on Event. e.g., • Database in which the event occurred • Object against which the event occurred • Transact-SQL statement of the event. • Captured using XQuery against the xml data that is generated by EVENTDATA

  8. DDL Triggers • Selecting a Particular DDL Statement to Fire a DDL Trigger • DDL Events - https://msdn.microsoft.com/en-us/library/bb522542.aspx • sys.triggers catalog view • DDL trigger can fire after execution of any T-SQL event belonging to a predefined grouping of similar events. • e.g. Specify FOR DDL_TABLE_EVENTS in the CREATE TRIGGER statement for DDL trigger to fire after on any CREATE TABLE, ALTER TABLE, or DROP TABLE statement is run • DDL Event Groups - https://msdn.microsoft.com/en-us/library/bb510452.aspx

  9. Example DDL Trigger • TriggerToCreateDatabase.sql • Event • Event Group

  10. SQLVer • Uses the EVENTDATA function to create a log of events. A DDL trigger is created on the current database that populates the table with event info whenever any database-level DDL event occurs - http://www.sqlservercentral.com/articles/version+tracking/119029/ • A lightweight DDL auditing solution • Alternative to Source Control • Requires discipline • Not easy to audit exact changes • Not easily exposed outside of source control environment • Not exactly fast • Real World

  11. SQLVer • Has a lot of other features, but main focus is Auditing feature • All objects use SQL schema SQLVER • 2 Main Tables:  • sqlver.tblSchemaManifest - populated automatically at installation with every SQL object in your database. • sqlver.tblSchemaLog. - A row gets inserted into tblSchemaLog any time you make a DDL change. • 3rd table sqlver.tblSysRTLog, not used by version tracking. • Uninstall SP - EXEC sqlver.spUninstall • EXEC sqlver.ver - to inspect the version information that has been logged

  12. Demo SQLVer

  13. SQLVer Mod 1

  14. References • https://msdn.microsoft.com/en-us/library/ff848766.aspx • https://msdn.microsoft.com/en-us/library/ff848799.aspx • https://msdn.microsoft.com/en-us/subscriptions/index/ms186406(v=sql.105).aspx • https://msdn.microsoft.com/en-us/library/ms173781.aspx • https://msdn.microsoft.com/en-us/library/hh213611.aspx • https://msdn.microsoft.com/en-us/library/ms187909.aspx • https://msdn.microsoft.com/en-us/library/ms175941.aspx • http://www.sqlservercentral.com/articles/DDL+Auditing/88433/ • http://sqlver.sourceforge.net/

  15. Thank You from PASSwww.sqlpass.org

More Related