SQLServerCentral - www.sqlservercentral.com

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

Featured Contents

Featured Script

The Voice of the DBA

The Future of Auditing

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


Video and Audio versions

Today's podcast features music by Everyday Jones. No relation, but I stumbled on to them and really like the music. Support this great duo at www.everydayjones.com.

Follow Steve Jones on Twitter to find links and database related items and announcements.

Steve Jones

Windows Media Video ( 19.5MB) feed

MP4 iPod Video ( 23.7MB) feed

MP3 Audio ( 4.6MB) feed

Feeds are available at iTunes and Mevio

To submit an article, rant or editorial,
log in to the Contribution Center

ADVERTISEMENT
SQL Toolbelt

16 essential SQL Server tools

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.

SQL DBA Bundle

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.

SQL Data Generator

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.

Featured Contents

 

Partitioned Views

Brian Ellul from SQLServerCentral.com

A look at Partitioned Views, their advantages, disadvantages backed up by examples and statistics. More »


 

SQLServerCentral apologizes and you can win a book

Press Release from SQLServerCentral.com

After a plagiarized article this week, a content for you. More »


 

SQL Server Database RESTORE WITH MOVE or not WITH MOVE

Additional Articles from MSSQLTips.com

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 »


 

From the SQLServerCentral Blogs - Myths and Misconceptions about Hekaton

Klaus Aschenbrenner from SQLServerCentral Blogs

(Be sure to checkout the SQLpassion Online Academy, where you get High-Quality SQL Server Trainings with Instant Access!) Over the... More »

Question of the Day

Today's Question (by Steve Jacobs):

In SQL Server 2005, is the code segment (a local variable in a stored procedure) below allowed?

DECLARE @a CHAR = 'A'

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: T-SQL.

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

ADVERTISEMENT

SQL Server Execution Plans

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

Yesterday's Question (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?

Answer: True

Explanation:

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.

Some references are: http://blogs.msdn.com/b/dqs/archive/2011/07/18/using-the-ssis-dqs-cleansing-component.aspx

http://blogs.msdn.com/b/mattm/archive/2011/07/14/overview-of-the-dqs-cleansing-transform.aspx


» Discuss this question and answer on the forums

Featured Script

Find all Job Relationships with SSIS Config files

Ray Sotkiewicz from SQLServerCentral.com

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

raysot @ comcast DOT net

More »

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 2014 : Administration - SQL Server 2014

I can not export all data from the database in mssql studio 2012 - Hello I opened - tastks - Exporting And have chosen the destination - unstructered (flat) file. But the wizard proposes to export only...

Limit DB access outside application - We have applications connected to SQL using windows authentication. While having connection with Application user can also access to Database...

Fusion-IO or Nimble? - We are finally setting up everything for a massive migration of all 2005 systems to 2012 with a "rollover" upgrade...

I have doubt whether it is going away. - If you read the Documentation (for Hekaton CTP1)released in June 2013, by Kalen Delaney, she mentions that In-memory OLTP components...

Pro Active DBA - Hi Guys, I've recently been appointed to maintain my companies internal DB's. Must also stress the point that the DB's are...


SQL Server 2014 : Development - SQL Server 2014

How to call procedure from function. Getting error Invalid use of side-effecting or time-dependent operator in 'INSERT EXEC' within a function. - Hi! I am trying to call procedure from function but getting following error. Invalid use of side-effecting or time-dependent operator in 'INSERT...


SQL Server 2012 : SQL 2012 - General

Replication Subscriber & Recovery Model - I have transactional replication setup on one database on a 2008R2 server. The subscriber is in FULL recovery mode, even though...

custom schema owner - So I have inherited a database with a few custom schemas - These are owned by a user who has sincle...

Eroor connecting from SSIS 2012 to mySQL databases - Hi all, I am currently setting up a SQL Server 2012 environment. In order to retrieve data from legacy systems I...

Regarding backups and recovery model - Hi All, One of my database recovery model is in simple. Is it possible to take up the transactional backup.(In my guess...

Not able to execute as owner for attached databases - Hi, with reference to http://technet.microsoft.com/en-us/library/ms188354.aspx [code="sql"] USE AdventureWorks2012; GO CREATE PROCEDURE HumanResources.uspEmployeesInDepartment @DeptValue int WITH EXECUTE AS OWNER AS SET NOCOUNT ON; SELECT e.BusinessEntityID, c.LastName, c.FirstName, e.JobTitle ...

Clustering on different subnets with AlwaysOn added on the top of it. - All SQL Guru's: We are setting up a DR solution on Windows 2008R2 and SQL 2012. There are 2 sub-nets (one...

DNS Aliasing, Reporting Services, and Kerberos - We're setting up DNS Aliasing on one of our SQL Servers, but I'm unable to get Kerberos to work from...

Cannot restore database. Cannot get exclusive accesss - While restoring database in sql server 2012, i am getting the following error in sql server 2012 "Exclusive access could not...

with VIEW_METADATA - SQL view have three view options 1) with schemabinding 2) with encryption 3) With view_metadata I have perfectly understood the first and second but...

AOAG: Node doesn't have a vote - When setting up my AOAG (primary + one secondary), I chose the "Node + File share witness". However, I get warnings that my...

High percent signal waits with very low cpu usage - I am trying to find out what could be causing this issue. Why would we be waiting on cpu when...


SQL Server 2012 : SQL Server 2012 - T-SQL

Slow Query Issue... - Hi All, I'm posting a query that is troubling me. The query is taking 3-4 mins and [i]not exists[/i] part...

SSIS package backup of sharepoint - Hi all, Hi was assigned a project to make a backup of data from 3 lists in a sharepoint with the...

insert into table execution of a stored procedure - Hello I have this table [b]declare @UserMidListTable Table (Id Int,Mid int,ValueMid int,CatParent int, [Enabled] int,LastUpdate Datetime,Company nvarchar(max))[/b] I want to fill it with...

Need a 2nd opinion on a query - I had a collegue write the following query. He got defensive when I asked what he was trying to accomplish...


SQL Server 2008 : SQL Server 2008 - General

How to find out who disabled/enabled a SQL agent job. - Is there a way to find out who enabled or disabled a SQL server agent job?

What Is Your Favorite Books For SQL Server Study - I mean what is a publisher from your perspective as a SQL Server professional produce a good collections of books...

Timeout error while connecting to SQL instance - Hi Experts, I have one server with SQL server 2005 and 2008 R2, I am getting timeout error while connecting to...

Import and Export Wizard Error(Importing data from Excel sheet to DB) - HI , While Importing data from MS_Excel to SQL DB by using import and export wizard i am getting below error...

Regarding backups and recovery model - Hi All, One of my database recovery model is in simple. Is it possible to take up the transactional backup.(In my guess...

Problem with an XML and complex logic? - Hi! I will be pleased if you can help me about my task. I have an input XML, which I have to...

Database Mirroring - HI All, In Production server, Principal database having 120GB log file(mirroring configured) Why ldf file is taking that much space, How I...

adding a filed in table is impossible!! - Hi I want to add a filed in my table , but I receive an error , this is : save as text...

Finding the RAID level - Is it possible to find the RAID config from Windows level? I mean for each of the drive in a cluster...

SSRS Subscription - Which User Set it Up? - Hi all, we have a generic login that we use to create all of our report subscriptions. There is a subscription...

linked server return from Oracle into table variable - <<previously posted to wrong forum> I'm trying to return the contents of a select statement on Oracle into a table variable...

Regarding tempdb usage - Hi, I need some help in tempdb files usage by user databases, I have two tempdb files, one in F drive(.ndf...

Execute SQL Proc - Hi Guys, I need to execute sql server proc from MS Access or Excel... the proc has couple of parameters, but...

Who is in the DBO group for all dbs. - Hi I need a script as part of a security audit. Any pointers would help. Even a table name where this stored...

Try to add a column - Hello SQL Guru's Im trying to add a column in a table but i get the following error; Msg 515, Level 16,...

Best way to monitor datafile and log file grow - Hi, what is the best way to monitor datafile and lofile grow ? With custom scripts ? Does perfmon can do that ? regards C

SSIS Package error - I just make a SSIS package and i get the following error. How can i fix it? It works before...

Restriction on Primary key - There is a restriction that we must have only one primary key per table. We can have composite primary key...

install SQL Server 2008 Client tools - Question: We are rolling out SQL Server 2008 R2 and I'm wondering if there is a way to install just...


SQL Server 2008 : T-SQL (SS2K8)

Help with SQL Query - Consider i have the below table structure [code="sql"] create table #temp(id int,entity nvarchar(50)) insert into #temp values(1,'Candy') insert into #temp values(2,'Chocolate') create table #temp2(name...

Weighted Average Sales by Customer - advice/strategy - We are a company that sells items and ships them in bulk. I am tasked with making a report in SSRS...

Using a Stored Proc to schedule a job - Can you use a stored proc to schedule a job? What I would like to do, is that when you...

file rename with store procedure - Hi all, I've files with an unix timestamp as name. For instance filename: 1081277409 I've written a function that based on the...

Designing for Performance and Integrity but have a Tran Problem - I have a DB that grows like crazy. This was a design decision I had nothing to do with, but...


SQL Server 2008 : SQL Server Newbies

INSERTION - HI all, I have a table TableA with structure: CREATE TABle tABLEA ( ID INT, SourceData nvarchar(MAX) ) Column source data contains INSERT query. I have...

6 hours for a delete task??? - For some reson, I have a lot of duplicated records, app. 2.500.000. I am deleting them with: delete FROm dbo.DutyRosterShiftHisto WHERE id...

Gathering Information - I am a newbie working as a DBA and wanted to pull together some information about my environment. I created...


SQL Server 2008 : Security (SS2K8)

Antivirus on large SQL Server instances - All, I'm having an issue where my current antivirus vendor is having issues when scanning my larger sql server machines....


SQL Server 2008 : SQL Server 2008 High Availability

Mirroring with Replication - I read an article called ReplicationAndDBM.docx maybe some recognize it? Any way I followed the steps and whenever the publication fails...


SQL Server 2008 : SQL Server 2008 Administration

Rebuild Index Maintenance Plan with Default Amount of free space - I Rebuilt Indexes on a Database with the Maintenance Plan and specified free up with the default amount of free...

Migration: detach & copy vrs backup & restore - I'm planning a migration of a third party app's databases to another server. The vendor is recommending the following steps:...

Unable to Start SQL Server Agent Service with sa privileges - I am having sysadmin privileges on a SQL Server 2008R2 Enterprise Instance. But not sure why I am unable to...

how to see Windows server information - Hello, I am seeking advice from experienced SQL Server administrators who have had to deal with administering SQL Servers when...

Job Activity Monitor will not open -- reason? - On my local installation of the SQL Server Management Studio for SQL Server 2008 R2 everything works fine [including the...

server automatic restart - We have a windows 2008 server sp2 that hosts a SQl server 2008. Recently the server has an automatically restart at...

Backup/restore full + differential - Hi all, we are upgrading an application, and I need to backup two quite big databases, and restore them to...

Restore Syntax - I'm upgrading a lot of Databases to SQL Server 2008 R2. I can't get the syntax right. I'm confused about what to...


SQL Server 2005 : Administering

Certificate Files - Hi all I'm in the process of the migrating a load of mdf files to a new volume before trashing the...

index defrag job failing - (Error 50000) start log size: 29233.2 [SQLSTATE 01000] (Error 0) max log size: -0.007808 [SQLSTATE 01000] (Error 0) [SQLSTATE 01000]...

Profiler Replay Very Slow - Stuck for Ideas - Hi, I'm doing some performance testing for a migration project and have created a trace of the system to be replaced....

Advise needed - Please pardon me first: I know this forum might not be the most fitted one but I also know this...

Linked Server Permission Problems - I have a linked server set up that works with my credentials, and it works with a low level SQL...

select query - i need a select query for below. there is 3 date colums in a table. how to find out the greatest value...


SQL Server 2005 : Backups

Backup failed - Hi all, I attempted to perform a backup and ended up with error message: [code="plain"] TITLE: Microsoft SQL Server Management Studio ------------------------------ Backup failed for...

Sequential number sql hourly tl backups - Hi All, I wonder if anybody could help with this problem. We are performing a full backup at 12:01 and then we...


SQL Server 2005 : Business Intelligence

SSRS open file instead of url within a report - I have a SSRS report that displays one field that contains a filename. I have been asked if it is...


SQL Server 2005 : SQL Server 2005 General Discussion

Database Password Protected - Hello , please can u tell me how to make my sqlserver 2005 database password protected. i make a database and i...

Can I remove these RAISERROR 44444? - We're working on migrating our database from SQL 2005 to SQL 2012. One issue that's come up is triggers, on...

True Aging Report - I have worked with SQL for many, many years and have never had the need to ask a question that...


SQL Server 2005 : SS2K5 Replication

ALTER TABLE breaks replication - I have a table-article being replicated as part of a publication that replicates DDL commands. Historically, executing ALTER statements on...

Remove a column in an article from Replication without reinitializing the replication - Hi, I have a Publication with 10 huge tables. I need to remove one column (for security reasons) from an article....

Will Transactional Replication Supports Simple recovery Model ? - Hi I got a doubt in Transactional replication . Wil it supports for simple recovery model databases? My doubt is transactions are...

(Source: MSSQLServer, Error number: 20598) - Hello i am having and issue with replication i am receiving the following Command attempted: if @@trancount > 0 rollback tran (Transaction...


SQL Server 2005 : SQL Server 2005 Integration Services

Writing single excel file dynamically to sql server table using script task - I am trying this on SSMS 2008 . Since there was no group as SQL Server 2008 Integration Services , i am...

SSIS Ignore Dupes - I apologize in advance as I'm sure this has been answered before. I did some searching and couldn't quite get...


SQL Server 2005 : T-SQL (SS2K5)

Column type conflict in Unpivot list - hi All, I am having problem with the unpivot. Below is the sample table and the sample data, I have also...


Reporting Services : Reporting Services

Create Customized Functionality in SSRS:s Report Server - Goal: Create an additional functionality in report server. It is a button or an address link and when you press it...

Procedure with default parameters - Guys, Unusually I'm using a production procedure in our of my reports, so we return *identical* data with the logic centralised....

Open microsoft outlook by clicking field on SSRS report - I know this is possible in Business Objects, maybe it is here too, but I cannot seem to figure it...


Reporting Services : Reporting Services 2005 Development

Rendering report as a PDF and printing the same byte array to printer - Hi, I am using Render Method to get the report as a byte array. After getting the byte array I...


Data Warehousing : Integration Services

Problems with dtsx deployment and UNC paths within dtsx and JOB Step definitions - Hi, (SqlServer 2008) I've got strange problems with dtsx deployment and UNC paths within dtsx and JOB Step definitions. [b]When trying to use...

data providers unavailable to vs - Hi. We run 2012 enterprise with vs2010. I have inherited a w7 machine whose vs 2010 installation seems to have...

Scheduling package in sql server 2008 r2 express edition - Hi, I have created a package in ssis and i need to schedule it every week. How do i do that...

SSIS OnError event handler is firing twice - Hi Experts, I'm having one or more Task(s) inside a ForLoop container.My req is to continue execution of the ForLoop event...

dtexec utility failing for package - I've created a package to load xls file to my DB table. While running the package from BIDS, it's running...

Maintain Delete flag in target table - Hi, I am pulling records from source table and inserting into target table. this is incremental load. If new records found...

Unable to send excel using Send Mail Task in SSIS - Hi, I have developed a package whose output is in Excel. I need to mail this excel to some users. i...

Easy Question: Organizing Data Flows - Since I'm new to this and data warehousing, I'm not sure what is the best way to do what I'm...

File exists for multiple specific filenames in folder - Hi. In a SSIS package I need to first check that all files exist that I need before I start processing...

How to copy data from multiple queries to same excel destination in ssis - Hi I have a requirement where i have data from multiple queries to be copied to a template in ssis. i...

Create table in MS SQL Server ffrom Quickbook - Hi Experts Can anyone please guide me how to create destination dynamically as its from Quickbook Source table.In Quickbook i...


Database Design : Design Ideas and Questions

Database Role Design/Standard - First, I apologize if this topic wasn't the right place for this post. I am looking for a good standard for...

Avoiding Large Number of db Columns in SQL Server - Database Redesign - I'm working on a database table in SQL Server 2008, which is the backend for a Classic ASP application. I...


SQLServerCentral.com : Anything that is NOT about SQL!

Fantasy football 2013 - I renewed the league, you should be getting an email soon. At the moment, there are no open spots, but...

Talking baseball - 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! - HI When you woke up today, or logged-onto Opera Forums, you may have had a dream, a thought, a scene...

Are the posted questions getting worse? - Is it me, or are the posted questions getting worse these days? I just read a post by someone apparently in...


SQL Server 7,2000 : T-SQL

Limitation of Varchar 8000 in executing a dynamic MDX inside stored Procedure - I have a Stored Procedure in SQL 2000 which build a Long MDX query .Then i connect to OLAP and...

This email has been sent to {user_email}. 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.
This transmission is ©2013 Red Gate Software Ltd, Newnham House, Cambridge Business Park, Cambridge, CB4 0WZ, United Kingdom. All rights reserved.
Contact: webmaster@sqlservercentral.com