In this issue

Featured Contents

Editorial

Featured Script

 
 advertisement
 
SQL Monitor The easiest installation of a third party tool, ever!
- Aaron Kolysko, Monsoon Commerce. Get started with SQL Monitor today to gain effortless insights into the health of your own servers - download a free trial
 
SQL Source Control Local evaluation repository makes trying SQL Source Control simple
The evaluation repository makes it easy to try SQL Source Control. Get started with the 28-day free trial.
 
SQL Storage Compress Compress live data by 73% 
Red Gate's SQL Storage Compress reduces the size of live SQL Server databases, saving you disk space and storage costs. Learn more.

In This Issue

TDE Setup and Administration Scripts

A comprehensive set of scripts to setup and operate TDE for a SQL Server. More »


Regular Expression Transformation

The regular expression transformation exposes the power of regular expression matching within the pipeline. One or more columns can be selected, and for each column an individual expression can be applied. The way multiple columns are handled can be set on the options page. More »


From the SQLServerCentral Blogs - Execution Plans … In the Cloud!

If you’re moving to a fully-fledged SQL database as part of Azure you may never even touch SQL Server Management... More »


Editorial - Separate Accounts

Many years ago I worked in a small company that only had about 5 or 6 servers. We had one system administrator whose job it was to manage all the servers. One day our sysadmin was on vacation when there was a problem with the Exchange server. One of the other developers worked on the system and ended up fixing it, but changed the service account password while doing so. The next day I walked into the office to find a group of people stymied as to what was wrong with the development server and version control system. Everyone claimed they hadn't changed anything on that server, and they were right. However our admin used the same domain account for all servers, including my SQL Servers. I changed the SQL service account that day.

One of the recommendations that I learned a long time ago, and one that I make regularly, is that every SQL Server instance should have a separate security account. In that case, I had separate accounts created for each database instance, and for each SQL Agent instance. We used long, random passwords that were never stored, and if we needed to access a password, we just changed it. That kind of flexibility and separation prevented any crosstalk issues between services, and it allowed us to easily alter permissions or passwords for one service without affecting any others.

The other day I saw someone recommending a single service account for all SQL Servers. Someone else recommended a single account for each version of SQL Server, using separate accounts where it's really needed. That's a better recommendation, but I still prefer completely separate accounts. I know that some security groups don't like that, but is it that big a problem? This Friday I wanted to ask you about your experiences.

Do you find separate accounts for each instance (or Agent) to be a security or administrative issue?

I'm not sure why this is unwieldy. Service accounts rarely change, and you could easily script changes to a group of accounts with PowerShell or some other tool. Once I set a service account, the only thing I might ever do later is alter the permissions to add access to a folder. When that happens, I definitely want to have separate accounts for each instance.

Let us know this Friday how you feel and what works for you.

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


The Voice of the DBA Podcasts

We publish three versions of the podcast each day for you to enjoy.

Everyday Jones

The podcast feeds are available at sqlservercentral.mevio.com. You can also follow Steve Jones on Twitter:

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. They have a great version of Message in a Bottle if you want to check it out.

I really appreciate and value feedback on the podcasts. Let us know what you like, don't like, or even send in ideas for the show. If you'd like to comment, post something here. The boss will be sure to read it.

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


Question of the Day

Today's Question:

Which of the following is true about the maximum database size in the SQL Server Express editions? Tick both correct answers.

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

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

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

The Microsoft Data Warehouse Toolkit: With SQL Server 2008 R2 and the Microsoft Business Intelligence Toolset

As the most influential thought leaders in the data warehousing and business intelligence industry, the Kimball Group has developed pioneering techniques that serve as industry standards for DW/BI system design, development, and management. With this new edition of their bestseller, veteran experts from the Kimball Group get you up to speed with using the new Business Intelligence release of SQL Server: SQL Server 2008 R2. Covering the complete suite of data warehousing and BI tools that are part of SQL Server 2008 R2, the authors follow the full project lifecycle, including design, development, deployment, and maintenance. Get your copy today from Amazon.


Yesterday's Question of the Day

Question: In what order do these events happen?

  1. Constraints, BEFORE triggers, INSTEAD OF triggers.
  2. INSTEAD OF triggers, constraints, BEFORE triggers, AFTER triggers.
  3. INSTEAD OF triggers, constraints, AFTER triggers.
  4. Constraints, INSTEAD OF triggers, AFTER triggers.

Answer: 3.INSTEAD OF triggers, constraints, AFTER triggers

Explanation: Instead Of trigger fired before all constraints and after trigger fired after execute statements and constraints

Ref: You can test it, but this is an explanation - http://stackoverflow.com/questions/7665022/t-sql-and-transaction-flow-from-first-to-last

» Discuss this question and answer on the forums

The Microsoft Data Warehouse Toolkit: With SQL Server 2008 R2 and the Microsoft Business Intelligence Toolset

As the most influential thought leaders in the data warehousing and business intelligence industry, the Kimball Group has developed pioneering techniques that serve as industry standards for DW/BI system design, development, and management. With this new edition of their bestseller, veteran experts from the Kimball Group get you up to speed with using the new Business Intelligence release of SQL Server: SQL Server 2008 R2. Covering the complete suite of data warehousing and BI tools that are part of SQL Server 2008 R2, the authors follow the full project lifecycle, including design, development, deployment, and maintenance. Get your copy today from Amazon.


Featured Script

Print The Calender 2

This script will print the calender from the given date. 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 2005 : Administering

No blocking, queries running slowly, what to check next? - Background: Database developer trying to fill in for an actual dba on a contract. Environment: Web app with very poor data...

Database Engine Tuning Advisor Permissions - Hi Guys, I have a question regarding Database Engine Tuning Advisor Permissions. Here in our shop, developers want to use DTA...

Database mail job failure notification - My question is related to Database mail job failure notification. The below job step used to run successfully until last...

SQL Server 2005 : Backups

Tran log does not get truncated in simple recovery - Hello All, We have a DB for which the Tran log became full. The DB is in simple recovery model. We...

LOG RESTORE AFTER DB. restore on new server with MOVE, NORECOVERY - Restore from server 1 db. name A, to server 2 db. renamed B, recovery: FULL so .bak + 10 log files -----...

sql 2005 and logshipping - hi, I have a basic question since years ago now, I did not find any solution. One database on one server name...

The database cannot be recovered because the log was not restored. - Hi, my db name is abc.. i hv restored the bckup on tht db.. But in middle i have cancleed the...

SQL Server 2005 : Business Intelligence

SSIS Package decryption SQL/Stored Proc - First, I apologize if there is something out there already as I have been unable to find it. Does anyone...

Anything out there better than SSRS - Hi All Beginning to look around for other BI tools. Don't have a vast amount of knowledge in this area, but...

Create dynamic feeds using SSIS - I am newbie for SSIS and found out that we cannot map columns directly into SSIS data flow. I have...

SQL Server 2005 : Working with Oracle

Refresh from Oracle - Hi friends, We need to refresh the data from Oracle database to SQL Server 2005 every hour or so.. We...

SQL Server 2005 : SQL Server 2005 General Discussion

Auto Save / Auto recover in Management Studio? - I have come in today to find my PC rebooted, I suspect due to windows updates, (it decides to reboot...

SQL Server 2005 : SQL Server 2005 Security

Transfer The Logins and The Passwords Between Instances of SQL Server 2005 - Hi All, I got an error after executing the script. Error: ----- Msg 208, Level 16, State 1, Procedure sp_help_revlogin, Line 11 Invalid object name...

SQL Server 2005 : SS2K5 Replication

Database Mirroring - Hi Gurus, Due to some network glitch mirroring connection is disable. Is it possible establish the connection automatically once network connected...

SQL Server 2005 : SQL Server 2005 Performance Tuning

Use of statistics - Stored Proc v Ad hoc SQL - I had a curious situation recently where a stored procedure ran for 48 hours without completing. Conversely, running the procedure...

SQL Server 2005 : SQL Server 2005 Integration Services

Getting the Duplicate rows? - Hi Friends, i am doing a project in SSIS 2008, SQL 2008R2... In the data flow, my source(OLEDB) has some duplicate records...

SSIS 2005 - Book suggestions? - The 2 main ones I've found online are: [ul] - Pro SQL Server 2005 Integration Services - [i][url]http://www.amazon.co.uk/Pro-Server-2005-Integration-Services/dp/1590598970[/url][/i] - Professional SQL Server 2005 Integration...

Timeout problem with PSEXEC in SSIS package when running as a Job. - Hi, I have an SSIS package with an Execute Process Task that calls a bat file to run a remote process....

Unable to Send Files to FTP Server - Hi, Sorry if the topic already exists. I want to process an excel report and send the file to FTP server....

ODBC Connection Manager - Hi, I'm trying to pull data from a Filemaker database into SQL. I have set up an ODBC connection and tested...

SSIS FTP Task - Hi, I have to copy 3 files daily from an ftp server.The files will have the name abcyyyymmdd.csv.Everyday I have to...

SQL Server 2005 : T-SQL (SS2K5)

Help - Need to fill in parent lot tool info for given table - Some background information is in order: 3rd -7th columns refer to tools that lots are processed through, in that order. I need...

Remove redundant data from address column - Hi, Consider the folllwing two records: [code="sql"]if object_id('test') > 0 drop table dbo.test create table dbo.test ( master_id int, Title varchar(20), Forename varchar(50), Surname varchar(50), Address1 varchar(200), Address2 varchar(200), Address3 varchar(200), Address4 varchar(200), Town varchar(100), County varchar(100), Postcode varchar

SQL Server 2005 : SQL Server Newbies

Excluding data from a result set - This statement calculates and returns the depreciated valus for a list of vehicles, The part i am now stuck on...

index with include column - hi, from the index recommendation scripts i ran, the indexes to be created are: 1. on column A 2. on column A include...

SQL Server 7,2000 : T-SQL

SQL 2000 Query to get time difference between 1st row and 2nd, 2nd and 3rd, 3rd and 4th and so on - I need help with SQL 2000 Query to get time difference between 1st row and 2nd, 2nd and 3rd, 3rd...

find out excel file modified date - Hello Friends I need to find out excel filet modified date. my task is that based on modification date = todaydate...

SQL Server 2008 : SQL Server 2008 - General

How to use group functionality in SSRS alongwith ORDER BY on the same field as Group? - In SSRS, i have to show total and sub total for one table field which is varchar. However i managed...

SQL Sever Client - I am working on sql server 2008. I don't want to give complete sql server 2008 to my clients. Is...

Top Ten Longest Running Queries question - I am using the following query on multiple SQL Servers with great success. Some of them have Offline databases and...

SQLCMD not in SQL 2008 R2 - I installed SQL 2008 R2 but there is no sqlcmd.exe. How to get it and make it available in CMD prompt?

get date part from a filename string - hey guys,am trying to write a sql to get the datepart from a filename in sql, the filename always varies...

measuring sql server effiency - I'm doing some analysis and trending on CPU utilization on a DB server which support an application which frequently changes...

Transactional Replication : initialize subscription from copied snapshot - large DB - Hello, I need to setup SQL Replication (transactional) on a database thats over 200 GB. Replication is going to be setup...

Best way to configure two merge replicated DBs, which are also synchronized with each other? - We are trying to accomplish the following: 1.DB1 is merge replicated to many clients running SQL CE 2.DB2 is also merge...

SQL is not utilizing an index it told me to create - So we had one SP totally dogging it. I CTRL-L it - and it says --The Query Processor estimates that implementing the...

Double Hop problem! - Hi, I'm trying to overcome a '[url=http://www.sqlservercentral.com/articles/Double+Hop/76743/]Double Hop[/url]' issue without any luck, and I'm hoping that someone can help identify where...

configuration error - hi, while enabling xp_cmdshell from value 0 to 1 we are getting the following error when we try to reconfigure it...

Which SQL Front End??? - Morning all, I was wondering what front ends people are using? I'm aware that I can use MS Access but would prefer...

Service acounts for SQL Server - Hi Experts, I got a request for creating 5 service accounts for sql servre Question 1) these accounts shoulb be AD...

Adding a new Node on the existing SQL Server two nodes cluster. - I currently have a two nodes SQL Server cluster. Both nodes have identical hardware (dell R900 servers). I want to...

SQL Browser service needed for non-standard static port? - If I am using dynamic port settings on my sql server instance, I need the sql browser service to run...

RSClintPrint - So we are in the process of converting over from Crystal to SSRS. Things seem to be going fine; however,...

Looking for guidelines on using the Tuning Advisor - So I'm looking to assist the developers at my employer, who have been working on an SQL based application. The...

blocking - Hello How to check blocking occurs in last days(means previous blocking) means i want to check transaction on Database for lat 3...

sql server 2000: how to limit a users session connecting to the database. - if a user connects from the application end to the database, he gets only 30 minutes of session time after...

SQL SERVICE - I have 3 instances on SQL 2008 , when i tried to restart one of the sql service of an instance...

Backup SQL Server file to remote Hard disk - Sir, I Want to backup sql server file in remote hard disk directly.But sql server takes only internal drives as...

Replication Issue. Inserting through a Trigger. - Hi All, I just setup the replication in my environment. Table structure. CREATE TABLE [dbo].[pwallet_tran]( [TId] [int] IDENTITY(1,1) NOT FOR REPLICATION...

Does the optimizer handle joining views to views? - I have read at least one post by Grant Fritchey stating that the optimizer can have issues when joining views...

Suggestion on how to implement a custom rollback - Hello Experts, I am trying to come up with a better solution for our intranet web application (running on SQL Server)....

List of all numbers between low and high - What is the best way to get a list of all numbers between low and high numbers? Thanks. Actually >= low,...

procedure started taking longer time to retrieve data in last two days - Hello, I was running a procedure to retrieve data which usually took 14-20 seconds but in last two days it started...

increasing duration for the same query over time - Please excuse the double post for very similar question- I think I was on the wrong track with my previous...

SSIS Transfer SQL Server Object Task - Hello, all! Due to database corruption referenced in a previous post, I am attempting to transfer all objects and data out...

Master Data Services Complicated Business Rules - We are investigating the use of Master Data Services at my company. One of the stumbling blocks I've run into...

Insert values from table 1 to table 2 - I HAVE TWO TABLE VOTERID and Deathtable Voter id table has the following field ISNID, Name,City,Alive I have attached the voterid table[img]Libraries\Documents\voterid.bmp[/img] I wanted...

SQL Server 2008 : T-SQL (SS2K8)

How to get inputer parameter value inside the stored procedure - Hi, I have stored procedure having one input parameter . ALTER PROCEDURE [dbo].[CMT_RoomDelete] @pRoomId INT, AS BEGIN UPDATE CMT_Rooms SET IsActive = 0 WHERE RoomID = @pRoomId END I want...

inconsistent of a select then update. - I have a store proc with 2 major parts in a while loop. A SELECT then an UPDATE The basic store...

How do I pad a delimited number? - I am trying to write a SQL statement to pad numbers with 0's. The number string has a . delimiter and...

Stored Proc to split data in two - Hey all, I have a stored proc. It does what i want it to - but it seems over complex and a...

Date Format Problem - Hi, I have a little problem where my Database stores Dates in 'yy/mm/dd' format. I want to retrieve Date in 'dd/mm/yy' Format. I...

Query Help- Cursor change into FOR WHILE LOOP, TEMP TABLE - I have created a STORED PROCEDURE, which includes 5 different CURSORs. The company I am working for has just informed...

Flattening a Parent Child Hierarchy - Hi, We are starting work on a BI project that is given data in a traditional Parent-Child Hierarchy format. The Problem is...

SQL Server 2008 : Working with Oracle

BIT datatype and Oracle - Hi, As per the business requirement, any SQL script that I make should follow ANSI standard, so that it should successfully...

Microsoft Connector for Oracle by Attunity - Has anyone installed Microsoft Connector for Oracle by Attunity? For Attunity 32 do you also need to install the Oracle 32-bit...

SQL Server 2008 : SQL Server Newbies

SQL Server 2008 R2 Express version with advanced services and Developer Edition - I've been working with SQL Server 2008 R2 Express version with advanced services and SQL Server Business Intelligence Studio to...

Can't connect to sql server from an XP machine, but I can from win7 - Hi there, I've been given access to a SQL Server and I wrote a VBA script (in excel) to query the...

Landing The First SQL Job.... - Hello All! I currently am a plumber by trade, been in that industry courtesy of my father in law since graduating...

Re-entering the DBA field - Looking for Advice/Suggestions - Hello Everyone, I'm a marketing professional looking to get back into the IT field that I left 10 years ago. I...

Enlisted or Drafted? Share your experience. - Some people ask to be a SQL Server DBA, some are drafted or rather are a "DBA by default". I...

Backup and save a copy on a server on a different domain - Hi I would like to set up a daily job that backs up the database and transfers a copy of the...

SQL Server 2008 : Security (SS2K8)

SQL Agent database role and xps - Hello, I recently performed a server migration and I'm left with some SQL Agent issues. I restored an msdb from one...

SQL Server 2008 : SQL Server 2008 High Availability

replication setup on cluster node - Hi, I have setup replication from sql server cluster group to standalone server. It's working fine but I am not able...

Migrate/Upgrade from Standard to Enterprise - Hi All, Please help/share your experience. Here are my requirements. [b]Current Environment[/b] SQL Server 2008 R2 Development environment 2 node Active/Passive cluster, Currently in standard...

mirroring Asynch suspended after i ran full backup - Hi there, My name is Leo, i am a junior DBA in my company. Nice to meet you all in this...

SQL Server 2008 : SQL Server 2008 Administration

Log Shipping - manual .trn backup - My log shipping is working fine but something I am unsure of. If I do a manual log backup of a...

Physically moving mdf/ldf files to different drive - Below is the code I am using to move files from C: drive to F: drive: ALTER DATABASE MyDB SET OFFLINE; GO --...

Index Fragmentation and Performance - Hi All I understand that External Fragmentation exists when the Index pages are not in a logical order for SQL Server...

Question about SQL Server, Memomy, Disk, Configuration. - Hi all, i cant think about an apropieted subject for the topic. Here is the thing. We have a Server with this...

TempDb free space - Today I found TempDb size has increased and disk space is very low. I need to get more free space...

Career : Resumes and Job Hunters

How can I attract Senior Database Administrator candidates? - I have a few full-time DBA positions open on my team, one of which is for a Sr. DBA. We...

Programming : Connecting

MySQL as linked server - Hi everbody I need some suggestions I Have a sql server(2005) default instance in Windows Server 2003, I need to connect...

Programming : XML

WITH XMLNAMESPACES y subconsultas - hi, i have a problem with xmlnamespaces. i try to explain my problem using a little example (information extracted from http://msdn.microsoft.com/en-us/library/bb510462.aspx) In...

SQLServerCentral.com : Anything that is NOT about SQL!

Crazy Interviews - Reading the topic 'Stupid Interviewer Tricks' reminded me of an interview where I couldn't tell if I was crazy or...

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...

SQLServerCentral.com : SQLServerCentral.com Website Issues

Scripts: SQLcode displays in 1 line - I try to submit a script. But when I paste the code it's displayed as 1 line. Tried to copy/paste it from...

Reporting Services : Reporting Services

SSRS blocks up the whole SQL2005 server - Hi guys, I have an subscription that normally runs in the evening. 84 reports are built by this subscription. It's done...

two values in Label in ssrs - How i can display two values in same label.but i want to show second value in bracket().i am using this...

Reporting Services : Reporting Services 2005 Development

SSRS 2005 Display Columns in a matrix report even if no data is present in the sql table - Hello, I have created a report in SSRS 2005 which contains a Matrix and I want to dislplay the following information: Row:UserID,...

Database Design : Design Ideas and Questions

Confused about how to design a new process (third week into new role) - Good afternoon, I'm not sure if I'm posting this in the right section but I could really do with some advice...

Data Warehousing : Integration Services

Creating a File in Script - Hi all, I am attempting to download zip files from a particular website and am having issues with[code]Dim mySSISWebClient As WebClient...

Transactional consistency in SSIS CDC - Hi all, I've been researching the different options that we have in building our data warehouse. I am intrigued by CDC...

Data Warehousing : Analysis Services

MDX - Cube Calculated member sliding 12 month comparison with previous month data - Hi, I want to compare Aggregate of following two different resultsets: [code="sql"] SELECT [Measures].[Internet Sales Amount] ON COLUMNS, NON Empty { LastPeriods( 12, [Date].[Calendar].[Month].[January 2008] ) } On Rows FROM [Adventure...

refresh Excel report based on Offline-SSAS-Cube (a *.cub-File) not possible - Hi, we can't refresh the Excel-reports based on a *.cub file even if there is a newer .cub-file. Background: We use SQL Server...