I was reading Captain's Share the other day and enjoying a quiet afternoon at home. It's a science fiction book about one man's journey in the future as a captain of a space freighter. It's an interesting series from Nathan Lowell that I've enjoyed and recommended to other science fiction fans. In the book, there's a scene where the main character is leaving his old ship as first mate and moving to a new ship as the captain. However he notes that the formal process is to deactivate his records on the old ship and ensuring they will be read only forever. The book notes they can't be deleted because they are a part of the ship's records, log entries, etc.
That seems to be a far cry from the way auditing takes place in current computer systems. Auditing of systems is under the control of the sysadmins (who are sometimes hackers) and can be altered, changed, etc. We, as software designers, haven't done a good job of ensuring the integrity and longevity of log records. In some sense, it seems to be a fundamental flaw in OS and software design to not have separated out the auditing and recording of actions from the administration and rights of the rest of the system.
I'd hope that we would recognize that auditing actions and preserving this data is something that ought to be tightly linked to, but separate from, the rest of system operation. I'd like to think that fundamental changes and actions taken on the system should be written separately to an area that is easily marked as readable by non-sysadmins that are designated to review the information. I know we have the challenges of managing the space and the problems of spurious actions being generated to fill (or rollover) logs, but I'd think after 50+ years of computing we would have considered some sort of event log that isn't under the control of the people whose actions it is recording.
SQL Server has improved its auditing features and capabilities, but far too much is still linked invariably to the sysadmin, often the same person the auditing should be watching. This is certainly one area that I hope matures in future versions as the need grows to track and review actions taken by privileged accounts.
In one installer, the award-winning SQL Toolbelt contains everything you need to work with SQL Server. "The SQL Toolbelt provides tools that database developers as well as DBAs should not live without." William Van Orden. Download a free trial.
Save 45% on our top SQL Server database administration tools.
Together they make up the SQL DBA Bundle, which supports your core tasks and helps your day run smoothly. Download a free trial now.
Generate realistic test data, fast
“In less than the time it took me to get my coffee, I had a database with 2 million rows of data for each of 10 tables.” Stephanie Beach, QA Manager. Try SQL Data Generator now.
Mike Eastland frequently restores production databases to various development servers. The drive layouts between the servers are almost never the same. He is constantly shuffling files on his development servers due to space constraints, which in turn breaks the automated restore scripts because the MOVE clauses are invalidated. Does he have to use the MOVE clause every time he does a restore? More »
SQL Server Execution Plans shows you what's going on behind the scenes in SQL Server. They can provide you with a wealth of information on how your queries are being executed by SQL Server, including: Which indexes are being used, and where no indexes are being used at all. How the data is being retrieved, and joined, from the tables defined in your query. How aggregations in GROUP BY queries are put together. Grab your copy today from Amazon!
Yesterday's Question of the Day
(by Igor Micev):
True or False: When you edit a DQS Cleansing component in SSIS 2012 package you must have a DQS server installed and a Knowledge Base?
DQS Cleansing is a transfomation component in SSIS introduced with SQL Server 2012 together with the Data Qualty Services.
In SSIS, by opening the Editor for a DQS Cleansing component you can see that you must specify a Data Quality connection manager and a Data Quality Knowledge base in order to use the component.
If you've ever had the pleasure of editing a job that calls other jobs, that calls even more jobs you understand what a headache that can turn into.
This script walks through the SYSJOBS and SYSJOB STEPS tables and builds the parent/child job hierarchy. It then analyzes each job step, looking for any SSIS job steps and subsequent config files used, if any. (This part can be easily changed to look for anything)
This was borne out of a need to change a rather large job hierarchy after one of our ETL servers failed and everything needed to be redirected to a new box.
Please feel free to email me if you have any questions/recommendations
Not able to execute as owner for attached databases
with reference to http://technet.microsoft.com/en-us/library/ms188354.aspx
CREATE PROCEDURE HumanResources.uspEmployeesInDepartment
WITH EXECUTE AS OWNER
SET NOCOUNT ON;
SELECT e.BusinessEntityID, c.LastName, c.FirstName, e.JobTitle
Help with SQL Query
- Consider i have the below table structure
create table #temp(id int,entity nvarchar(50))
insert into #temp values(1,'Candy')
insert into #temp values(2,'Chocolate')
create table #temp2(name...
- Hi all,
I attempted to perform a backup and ended up with error message:
TITLE: Microsoft SQL Server Management Studio
Backup failed for...
Fantasy football 2013
- I renewed the league, you should be getting an email soon. At the moment, there are no open spots, but...
- Okay, a topic that has NOTHING to do with SQL . . .
Came across [url=http://www.sqlservercentral.com/Forums/Topic447796-4-1.aspx]this link[/url] describing the SSC point scoring descriptions,...
Today's Random Word!
When you woke up today, or logged-onto Opera Forums, you may have had a dream, a thought, a scene...
This newsletter was sent to you because you signed up at SQLServerCentral.com.
Feel free to forward this to any colleagues that you think might be interested.
If you have received this email from a colleague, you can register to receive it here.