SQL Server recently changed objects

List recently changed procedures and views in a database: SELECT name , create_date , modify_date FROM sys.objects WHERE ( create_date >= DATEADD ( DAY , -7 , GETDATE() ) OR modify_date >= DATEADD ( DAY , -7 , GETDATE() ) ) AND type_desc IN ( ‘VIEW’ , ‘SQL_STORED_PROCEDURE’ ) ORDER BY name

Read More SQL Server recently changed objects
October 25, 2013

Public Database Links security fix using views

Public database links are a well known security problem – all database users with the most minimal privileges (create session) are able to use the public database link, even including deleting data across the link. Use this to see any public database links going outwards from a database: col host form a30 col db_link form […]

Read More Public Database Links security fix using views
October 24, 2013

PL/SQL to find and lock or drop user accounts

Script to hunt and lock/drop user accounts, can call from a central script connecting to multiple databases: BEGIN FOR d1 IN ( SELECT username FROM dba_users WHERE username LIKE ‘AGXDL%’ AND account_status != ‘LOCKED’ ORDER BY 1 ) LOOP dbms_output.put_line ( ‘alter user ‘ || d1.username || ‘ account lock ‘ ) ; execute immediate […]

Read More PL/SQL to find and lock or drop user accounts
October 10, 2013