DATABASE_OBJECT_CHANGE_GROUP do not audit SP or other object

Deepak Kumar (friend of mine and founder of http://www.sqlknowledge.com )  were chatting yesterday. We were discussing about audit feature in SQL 2008, Deepak has enabled this feature for one of his client since month. And when he was looking at the log he found that there were entries but they are related to Tables only, and not other objects like SP(s). 

We were discussing and google about the same and found an entry in connect where in it was answered.  

If we need the other objects to be audited we have to add SCHEMA_OBJECT_CHANGE_GROUP to the audit specification. Here is an excerpt from the connect:

Thanks for your feedback. The behaviour you're seeing is by-design. In order to audit CREATE/DROP of an SP, you need to add the SCHEMA_OBJECT_CHANGE_GROUP to your audit specification. The DATABASE_OBJECT_CHANGE_GROUP is actually auditing the ALTER permission check on the SCHEMA as part of the CREATE statement. 

You can find the complete entry and more information at connect article  


-- Hemantgiri S. Goswami (http://www.sql-server-citation.com )

Read More

Refresh QA Database with Manual Scripts

Couple of week back Megha Sharma send me an email with a document attached, this document is all about how to refresh QA database with manual script. Here is the preview of the document, and the reason why we should follow the method in her own words:

QA environment frequently needs Database refresh and hence space on its disks. QA Database is refreshed from Production followed by a data purge, which leaves a lot of free space in the database. In order to release the free space, we follow database shrink command, a lengthy & single thread I / O operation, also cause high data fragmentation. To avoid this resource & time consuming activity, we do a Refresh Database with manual scripts, in which, we create a new database, transfer tables, data & other objects via scripts. The Generate Scripts option of a database generates script for the complete database and transfers data (using Insert into command, highly logged), to avoid this we do a Select * into, to transfer table definition & data (minimal logged & fast, being a bulk operation) & then generate Table Objects (Keys, Constraints, Triggers, Indexes) via manual scripts & other database objects (Views, Stored Procedures, Functions, Users, Roles, Schemas) via generate scripts.

Sounds interesting ? Want to download the complete document ? Download Link

Please post back your review in the comment section here.


-- Hemantgiri S. Goswami (http://www.sql-server-citation.com )

Read More