A community of more than 1,600,000 database professionals and growing

The Voice of the DBA

The Future of Auditing

This editorial was originally published on Dec 5, 2013. Is it being re-run as Steve is on holiday.

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.

Steve Jones from SQLServerCentral.com

Join the debate, and respond to today's editorial on the forums

SQL Toolbelt contains the industry-standard products for
SQL Server:

Development

Deployment

Backup

Monitoring

Question of the Day

Today's Question (by Steve Jones):

Which keys actually encrypt the data in Always Encrypted?

Think you know the answer? Click here, and find out if you are right.

We keep track of your score to give you bragging rights against your peers.
This question is worth 1 point in this category: Always Encrypted.

We'd love to give you credit for your own question and answer.
To submit a QOTD, simply log in to the Contribution Center.

Professional Microsoft SQL Server 2014 Integration Services

The 2014 release of Microsoft's SQL Server Integration Services provides enhancements for managing extraction, transformation, and load operations, plus expanded in-memory capabilities, improved disaster recovery, increased scalability, and much more. The increased functionality will streamline your ETL processes and smooth out your workflow, but the catch is that your workflow must change. New tools come with new best practices, and Professional Microsoft SQL Server 2014 Integration Services will keep you ahead of the curve. SQL Server MVP Brian Knight is the most respected name in the business, and your ultimate guide to navigating the changes to use Microsoft SQL Server Integration Services 2014 to your utmost advantage. Get your copy from Amazon today.

Yesterday's Question of the Day

Yesterday's Question (by Steve Jones):

In SQL Server 2016, what is the string input size limit for the HASHBYTES function?

Answer: string size limit in SQL Server 2016 (2^31-1)

Explanation:

Prior to SQL Server 2016, the input limit was 8000 characters. In SQL Server 2016, the only limit is the system string size limit of 2^31-1 characters.

Ref: HASHBYTES() - https://msdn.microsoft.com/en-us/library/ms174415.aspx

Max Capacity Specifications for SQL Server - https://msdn.microsoft.com/en-us/library/ms143432.aspx#Engine

» Discuss this question and answer on the forums

Database Pros Who Need Your Help

Here's a few of the new posts today on the forums. To see more, visit the forums.

SQL Server 2016 : SQL Server 2016 - Administration

how to restore 12 gb backup file it on sqlserver 2012 - I have 12.5 GB database backup file.i want to restore it on SQL Server 2012 ,when i m restore database...

SQL Server 2016 : SQL Server 2016 - Development and T-SQL

Causing the Job to fail based on a condition - I have a package which loads a file to staging table and rename the filename.Now i am trying to work...

Calling a Insert/Update Stored Procedure from a Result set of Query - Hi, I have created a stored procedure(1) to do an insert / update when a parameter is passed. i have a table...

Import data from SQL Server to Vertica - Hello SSC, I have been struggling trying to import data from SQL to Vertica using CMD shell. Does anyone have experience...

SQL Server 2014 : Development - SQL Server 2014

Query help filling in dates and data - I have a data table and a calendar table. I need to fill in the gaps to have a row...

Query help - Maybe my skull is a little too thick today but I cannot figure out why this is not grouping correctly: SELECT...

GROUP BY in SQL Server 2014 - Hello all, The same query with a group by clause I try to run on SQL Server 2005 and on SQl...

How to extract overlapping date ranges from a table of date ranges - Would appreciate help and or hints to the following scenario:: Magazine subscriptions are registered on a per subscriber basis with subscription...

SQL Server 2012 : SQL 2012 - General

Help sought with "reading" a log file (looking for who deleted something) - I have a situation where somehow, an entire table of records were deleted from a database. I restored a backup...

Help with Query to Look at the list and return repeated value if condition is met - Hi Everyone I asked a similar question in my previous post but didn't get the right result , although it is nearly...

SQL Server 2012 : SQL Server 2012 - T-SQL

Quick question regarding UNION - Hi all, A colleague of mine has just asked me if I can explain why a SQL statement he's written that...

Parsing paired relationships. - Hmmm... OK, I can do this but only with some really expensive RBAR table operations, the script I've written takes...

SQL Server 2008 : SQL Server 2008 - General

Reindex DB on regular basis - Hi all! I have a database where app. 10.000 records are deleted/updated/inserted pr. day. I have understood from other sources, that i...

Error converting data type nvarchar to float - Hi Error converting data type nvarchar to float is returning Select Convert(mycolumn as float) from mytable; mycolumn is nvarchar(100) but my destination...

SQL Server 2008 : SQL Server Newbies

SQL Reproting Failed to query - Hi Good Day, Kindly please help me on my problem regarding the reporting services of SQL server. I have a...

Reporting Services : Reporting Services 2008 Development

Reporting Services - can not deploy report, already dataset exists - I am using SQL Server 2008R2. I created a report that uses a shared dataset. I created a new dataset...

Reporting Services : SSRS 2014

Warning 1 [rsInvalidColor] The value of the BackgroundColor property for the page ‘Page’ is “Light Green”, which is not a valid BackgroundColor. - I am following examples/exercises in this book: https://drive.google.com/open?id=0ByFnKe4djAO-d29ZTUJrbExhbzQ page 238. I am getting an error: Warning 1 [rsInvalidColor] The value of the...

Data Warehousing : Strategies and Ideas

Request for review of Star schema design - Hi all, I'm new to Data Warehousing, and could do with some comments on a star schema design I've created....

SQL Server 2005 : Administering

Notify multiple operators - Please suggest how to notify mutiple operators using database mail for failed jobs. Also entering email addresses 1@a.com;2@b.com....etc is not...

SQL Server 7,2000 : General

Why Shouldn't I use Linked server? - Hello Everyone, I have 4 SQL database servers (all using SQL Server) and need to communicate between them for lookup...

This email has been sent to newsletter@newslettercollector.com. To be removed from this list, please click here.
If you have any problems leaving the list, please contact the webmaster@sqlservercentral.com.

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.