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

Elevation of Privileges

At SQL Bits this year I attended a security presentation from Andreas Wolter. The session examined some attack methodologies, showing the flow that an attacker might go through to gain information about your database instance with SQL Injection. It's a scary and eye-opening talk, and one that I might recommend to all DBAs and developers so that they can understand the dangers involved with poorly coded applications.

One of the most scary attacks was the elevation of privileges from a web user to a sysadmin on an instance, mainly because of the Trustworthy setting being enabled. I had never imagined this as an attack vector, but it was disconcerting to say the least. However it got me wondering about instances I've managed.

Would I detect if a new sysadmin were added? Or an existing user added to the role? I'm not sure I would, though that's certainly something I plan on setting up with some sort of monitoring to detect. I would guess that most DBAs, whether professional or accidental, might not catch this either, at least until some audit was performed. At that time it might be too late to protect your data, and certainly too late to protect your reputation.

Security is a tough topic, and it's an ongoing process to protect your systems. I hope to see more presentations like this at future events, and I'd encourage you to request them for any events you plan on attending. You can certainly do this for all SQL Saturday events (there's a suggest a session on the schedule page). 

Security requires vigilance and vigilance requires monitoring. Both of those also need knowledge, so be sure that you don't neglect the security of your SQL Servers and continue to educate yourself over time as well as implementing technical solutions.

Steve Jones from SQLServerCentral.com

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


The Voice of the DBA Podcast

Listen to the MP3 Audio ( 3.5MB) podcast or subscribe to the feed at iTunes and LibSyn. feed

The Voice of the DBA 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.

Everyday Jones

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


ADVERTISEMENT

Come learn about Continuous Integration for databases at our Cambridge workshop on Friday, August 8, 2014 at the Red Gate office. Learn to link your source control system to a build environment and automatically generate and test your databases. We will use Subversion and Jenkins in the class, and explain how the techniques can be applied to any other VCS (Git, TFS, Mercurial, etc) as well as any build server (Bamboo, Team City, TFS Build, etc.)

You can register for the workshop here

ADVERTISEMENT
sqldbabundle

The seven tools in the SQL DBA Bundle support your core SQL Server database administration tasks.

Make backups a breeze! Enjoy trouble-free troubleshooting! Make the most of monitoring! Download a free trial now.

Just Azure

Struggling to understand Azure Storage Queues?

Microsoft Azure Queues are a ready-to-use service that loosely connects components or applications through the cloud. Roman Schacherl has written an introduction to this service to help get your started. Read the free article here.

Ricky Leeks on Learning .NET Memory Management

Ricky Leeks on Learning .NET Memory Management

Pick up all six free articles in one free download. Find out what Ricky has to teach you about garbage collection, memory management gotchas, and more. Download the article pack free.

Featured Contents

 

Data Driven Security

Andre Quitta from SQLServerCentral.com

Set a security standard across environments that developers can see and run, but not change. More »


 

Free eBook: Defensive Database Programming

Press Release from Red-Gate

Resilient T-SQL code is code that is designed to last, and to be safely reused by others. The goal of defensive database programming, the goal of this book, is to help you to produce resilient T-SQL code that robustly and gracefully handles cases of unintended use, and is resilient to common changes to the database environment. More »


 

SQL Server Alert for Tempdb Growing Out of Control

Additional Articles from MSSQLTips.com

One option to get notified when TempDB grows is to create a SQL Alert to fire a SQL Agent Job that will automatically send an email alerting the DBA when the Tempdb reaches a specific file size. This tip explains how to set it up. More »


 

From the SQLServerCentral Blogs - Quick Tips–SQL Prompt Custom Aliases

Steve Jones from SQLServerCentral Blogs

I love SQL Prompt, and think it’s a great productivity tool. Even before I worked at Red Gate, I love... More »


 

From the SQLServerCentral Blogs - Make an SSIS package Delay or Wait for Data

MikeDavis from SQLServerCentral Blogs

Packages can be scheduled to run at a time when you expect data to be in a database. Instead of... More »

Question of the Day

Today's Question (by Sreepathi):

DECLARE @i INT = 1
WHILE @i < 5
BEGIN
    DECLARE @j AS TABLE
    (
        i INT
    )
    SET @i = @i + 1
    INSERT INTO @j
        SELECT @i   
END

SELECT * FROM  @j

If I run the above query, what will be the result set using SQL Server 2012?

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

Professional Microsoft SQL Server 2014 Administration

Microsoft's SQL Server 2014 update means big changes for database administrators, and you need to get up to speed quickly because your methods, workflow, and favorite techniques will be different from here on out. The update's enhanced support of large-scale enterprise databases and significant price advantage mean that SQL Server 2014 will become even more widely adopted across the industry. The update includes new backup and recovery tools, new AlwaysOn features, and enhanced cloud capabilities. In-memory OLTP, Buffer Pool Extensions for SSDs, and a new Cardinality Estimator can improve functionality and smooth out the workflow, but only if you understand their full capabilities. Professional Microsoft SQL Server 2014 is your comprehensive guide to working with the new environment. Get your copy from Amazon today.

Yesterday's Question of the Day

Yesterday's Question (by free mascot):

How is a row is stored in memory-optimized table storage?

Answer: Rows are stored as individual rows.

Explanation:

The correct answer is "Rows are stored as individual rows". 8K pages are part of Disk-based storage. Refer following link for detail:

http://msdn.microsoft.com/en-us/library/dn553126.aspx


» Discuss this question and answer on the forums

Featured Script

How long will In-Recovery database take to come online 2005/2008/2008R

Patrick Akhamie from SQLServerCentral.com

Copy the Script, change the database name as seen in the first line and then execute it.

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

Disable Unicode output - I am running SQL Server 2012. I have been producing files as output for the Unix folks at work. We...

Can SSIS 2012 run against SQL 2014 database? - We are considering installing SQL 2014 to take advantage of columnstore updateable indexes for an upcoming ETL process but am...

Timeout expired. The timeout period elapsed - I am currently running SQL 2014 in my production environment. Both machines are configured for Windows Failover Cluster Services so...

About Index - Hi Experts,

Restore database without backup history in msdb - What is the best way to restore a database from a folder of backups (including full, diff and log backups)...

Upgrading SSRS from 2000 to 2008 - I am trying to upgrade SQL 2000 reporting services to SQL 2008. I backed up the ReportSever and ReportServer TempDB...


SQL Server 2014 : Development - SQL Server 2014

need repetition of a table when joined with another table - table1 id value 1 11 2 12 3 13 4 14 table2 id1 value1 1 21 2 22 1 31 2 32 in need output as follows id value id1 value1 1 11 1...

How to keep Canadian and US customers database separate in their country - We have a need to keep MS SQL server data of users in their country, USA and Canada. We have...


SQL Server 2012 : SQL 2012 - General

Replication - I've a database that comprises of about 50 tables. Some have PKs and some don't. Source is 2005 and Target...

Selecting a Clustered Index - HI Guy The question is a bit allover - What are your thoughts on adding clustered index on datetime (createdDate , native GUID)...

SSIS OleDB command parameter problem - I have a OLE DB Command Task that should update a column of a number of rows in a table. It...

Restore Master Database for a new dba - Sometime during the night last night some user account permissions were "lost". Am I right to think that restoring the...

Distinct Partition Functions? - Hi All We are planning to partition some of the huge tables that we have which hold records in the magnitude...

Upgrade Path from SQL Server 2008 - Hi, Can I upgrade from SQL Server 2008 (NOT 2008R2) to SQL Server 2012 is there an upgrade path for this?...

SSIS package config issue - Hello, I recently upgraded several SSIS packages from SQL 2005 - 2012 using the package upgrade wizard. All SSIS packages connect via...

SQL Server 2012 Transactional replication - Hi experts, I have two standalone sql instances on same domain with named instances and they are listening on 2 different...

Parameter error when executing a package with the built in stored procedures - I am using Excel VBA to run a stored procedure which executes a package using the built-in SQL Server stored...

RAISE ERROR to THROW conversion - Hi, I have an old proc like below which I want to update to use THROW. Looks like THROW can save...

Backup / Restore Availability Group following fail-over - I have 2 servers in a SQL Server Fail-Over Cluster. IOW I use always-on availability groups. I run backups - full,...

How useful are saved configuration .ini files for automating installs? - I nabbed a couple from some recent installs I did, and, assuming I can get my SAN guy to always...


SQL Server 2012 : SQL Server 2012 - T-SQL

Error Msg 102, Level 15, State 1, Procedure - Can anyone help me. I use a script which run in 2008 without error. But in 2012 i got this error:...

how to move data from one table to another table - i have a table attendance_details in both database DB1 and DB2, i need to move 01/7/14 and 02/7/14 records from...

Pre 2012 Stored Procedure: Need some DEV advice (COMPUTE BY TO WITH ROLL UP) - It's not my program, but it's a really nice Stored Procedure that was written by Richard Ding back in 2008...


SQL Server 2008 : SQL Server 2008 - General

Name table - Hello, My search skills are poor at best, so I was not able to get very far. But I have...

How to create a job targeting multiple servers? - I need to create a job that would run like this: Step1 -- server A Step2 -- server B Step3 -- server A Step4 -- server B In Job...

Query Timeout expired - A .Net 4.0 Web application uses both SQLNCL10 and ODBC to connect to a SQL Server 2008 R2 Standard Edition...

Running a stored proc remotely - Hey all! I've been trying to look for a solution to this but haven't really been successful. Basically, I have a stored...

Linked server with MS access mdb database - Error 7416? - Hi MS ACCESS mdb database located to another server. SQL 2K8R2 - database another server. I have created & working fine linked at SQL 2K8R2...

Merging XML Variables Like a Table Join - Hi All, I have several stored procedures that will query tables and return xml data. I would like to join these...

Resource Governor Bug - Has anyone notice a bug on the 2008 version of resource governor, where the reported CPU usage is different when...

EXEC sys.sp_expired_subscription_cleanup - EXEC sys.sp_expired_subscription_cleanup throwing error Msg 18482, Level 14, State 1, Line 1 Could not connect to server 'DEV1' because 'DEV2' is...

OS cache in Sql Server - Does Sql server use OS cache (outside buffer cache) for its operations ?

How to optimize this query? - Hi All, I have a simple query as below Select A,B,C from Table1 How to create a good index on that...

CHECK CONSTRAINT statements added to my SP - SQL Server 2008 R2 I opened one of my Stored Procedures today to find a long list of statements like the...

Building a Large XML file to Include or Exclude Groups and Detail According to Parametrised Info - Hello all, I am trying to create an xml file for our customers, based on information stored in our database. The...

Multiple joins to the same table - Hi all I've got the following stored procedure (this is part of the from clause) and it looks horrendous. Here's the...

prerequisites for replication between SQL Server and Oracle Database - Hi, Wanted to know the prerequisites for replication between SQL Server and Oracle Database. In this SQL Server is the publisher...

Unspecified error using ado command to update a varchar max field - Hello... I posted this a couple of days ago on the "anything that is not SQL" forum but had no...

SSIS package turns yellow and stops. - Hello all, I have a SSIS package that consists of a Foreach file Enumerator and a Foreach ADO.NET Schema Rowset Enumerator....

easy tool to capture data changes - Hi, Is it possible to enable CDC on[b] all tables[/b] in the database ? also is it possible to find which tables...

Tuning a query that takes 60 minutes to run, - Hello, I have a SQL query that takes 60 minutes to complete, the query is selecting data from 4 tables, out...

SQL procedure Query Performance issue - Hi , I have a issue regarding a query showing very late response on one server while it works perfectly on...

Display Duplicates - I have a Contact db. It has the usual Columns: Company, FirstName, LastName, Address, City, ST, etc.. When I SELECT DISTINCT on...

DB transaction log file on a shared hosting service - Hi, Thanks to this forum. It's great. I have a SQL DB on a shared hosting service (NewTek/The SBA) with 400Mb disk...


SQL Server 2008 : T-SQL (SS2K8)

Returning MAX(Value) in String - Hello comunity I need help to create a TSQL to return MAX(Value) removing the first part and last part. Example i have these...

T-SQL to get value on any given day - I have a table that records when a value changes. For example item = x, date = 2014-06-15, value = 0; item = y,...

Filtered index "AND" and "OR" - I am trying to create an index for a specific query that is used a lot. Unfortunately I am keep getting...

SQL Query Help Please - Hello Everyone, I need to write a report where I query against my table that holds employee's Pay Data, I look...

T-SQL to add 7 days to ship date - Hello All, A quick question to all regarding my query. Below statement adds 7 days to the shipment date if we...

Conversion from varchar to strong data types - Hi all, There are a few databases I work with that have been designed where varchar columns are used to store...


SQL Server 2008 : Working with Oracle

Error when selecting top N records from oracle - I am writing a large select statement in MS SQL to select data from linked Oracle 11g server. Started with this e.g. Select...


SQL Server 2008 : SQL Server Newbies

Returning multiple values from a single value - I'm trying to write a query that will return three related values from a single value. My input data in it's...

Parsing a String in SSRS - What up Guys, First time posting a question here. I'm not entirely sure how to solve. I have the following string...


SQL Server 2008 : SQL Server 2008 High Availability

Long Running Restore-Redo Step for LOG RESTORE - Hello, We have been getting unexpectedly long restore times against a couple of databases and was hoping someone could point me...

'Best' HA/DR option to protect against data corruption - Hi All, I know this may seem like an open-ended question but last week we encountered data corruption (eventid 824) and...

Process for Dropping & Readding articles in Trasnsreplication - Hi, I have a Publisher (Pub_A) with two subscribers (Sub_A/Sub_B), both are transactional replication with pull subscriptions. The publication has ten articles....

After sql cluster node started - 4hrs later sql DBI service terminated - During a ms patch maintenance window, 2 node sql cluster, 1 node shutdown and patched and rebooted, 2nd node shutdown...


SQL Server 2008 : SQL Server 2008 Administration

Indexs dont work - I have a weird issue.. There were a few tables i had replaced with new data. the table structure is same just...

Live P2V SQL Server - Hello, Has anyone ever done a live physical to virtual conversion? If so, were there any special considerations or planning steps...

SQL Server Logs - Hi, How do I delete the Archive SQL server logs? Many thanks

Cluster drive issue - Running out of space on active/pasive cluster (physical servers attached to SAN). netops added new drive, I see the new...

I want to check the 7 Days backup status... - Hi All, If I want to check the 7 Days backup status means how can i check? Regards Jagadeesh...


SQL Server 2008 : SQL Server 2008 Performance Tuning

quick sp_whoisactive question? - Hello everyone, i'm using sp_whoisactive to troubleshoot a perf problem. I'll probable be posting questions about the specific problem later...


SQL Server 2005 : Administering

Does anyone know of a way to get the machine's processor name from T-SQL? - I'd like to retrieve the processor name (the same that name that shows up in the system properties control panel...

Will the application work as usual if we create alias with existing cluster name for a new sql server instance and break cluster ? - Hi Experts, One of the SQL Server 2005 cluster database is being moved to a new standalone VM instance. Application team has...

Get column names in first row of result set - Hello, Does anyone know of a handy trick to get the first row of the results of a Select statement to...

Restore Database Failed: 'Exclusive access could not be obtained because the database is in use.' - Hi, While trying to restore a database (with replace option), I am getting the below error - Exclusive access could not be...


SQL Server 2005 : Business Intelligence

SSIS Packages Real Time Work? - May i know What is The Purpose Of SSIS Packages Related to any One Real Time Project With Example?

SSRS-Adding a subgroup to a group column - Here is my scenario: I have a matrix report which looks like this: Project Hours Normalized Hours ProjectXYZ 10 20 --------------------------------------- ProjectXYZ...

If count of records in query >0, proceed, else stop SSIS task and email - Hello, I'm new to SSIS and trying to figure out this seemingly simply task......what would be just a few lines...

Some problem in SSIS 2008 when Excel is used - Hi friends, Is there any known issue with EXcel source/ destination in SSIS 2008 ? I have got the below error when...


SQL Server 2005 : SQL Server 2005 General Discussion

Delete statement not delting rows? - Has anyone come across a situation where a delete statement doesn't give an error, but also doesn't delete the rows...


SQL Server 2005 : SS2K5 Replication

In transaction replication, do we need to backup subscriber database - Hi! I am new to replication. I have transactional replication setup between two servers. Do I need to create Full and T-log...


SQL Server 2005 : SQL Server 2005 Integration Services

format the output column in destination .csv - Hello, in ssis, I use a oledb source to execute a stored procedure and a flat file source to have the...

how do you break out of a foreach loop - I've got a foreach loop container looping through a folder that has zipped files with both a 2014-06-20 and 2014-06-21...


SQL Server 2005 : T-SQL (SS2K5)

XP_cmdshell DIR - Hi experts, I was wondering if there is an alternative to running XP_cmdshell DIR. Basically, I run xp_cmdshell to gather file info...


Reporting Services : Reporting Services

date a week - need date for weeks, beginning the week and end of week format. ie for week 1 week 2 week ... 52....

Setting arithabort on in SSRS - Anyone know if this is possible? Have been consistently using a plan which does not have arithabort even though I...


Reporting Services : Reporting Services 2008/R2 Administration

Can't connect to SSRS instance from SSMS - When trying to connect to my SSRS instance from my SSMS on my desktop machine using the servername\instance name format,...


Reporting Services : Reporting Services 2008 Development

Paging A Stacked Bar Chart - What our users want, sorted largest - smallest on the x axis for y axis value. I tried the put chart in...

Opening SubReport with JavaScript - I have a slightly weird situation with a subreport. We have live and failover Reporting Servers and the reports are...

SSRS Matrix Sort query - Good Afternoon I have a matrix within SSRS I am trying to sort on. My matrix looks like as below: --Months-- ...


Reporting Services : SSRS 2012

SSRS 2012 have Issue with Preview - Hello Experts I have problem with preview reports on vs2012 using framework 4.5 and run the VS by RunAs command. I added...

Asking for Credentials (Authentication) while trying to open Report Manager. - Hi All, New to Reporting (using ssrs 2012). Tried to open Report Manager using http://<my machine name>/reports It's asking for Authentication... UserName...

ReportViewer Control Paging Not working ... - Hi, I am using a report viewer control in a aspx webform in local processing mode and running IE 8. I...

One report Multiple Subscription Daily & Weekly - What is the best way to deliver this report, I have in the past just created another report for weekly,...


Programming : Powershell

email if no new files in last 2 days - I have this code that works great if there is a new file in the last 23 hours. how can...


Data Warehousing : Integration Services

SSIS checking datatype Int - Hi all I have been trying to check a row of data that is coming from an SQL DB. It should...

Common encountered errors in SSIS - Hi, I am currently writing my Bachelor Thesis about error handling in SSIS and generally increasing the robustness of ETL processes. One...

SSIS on SQL Server 2008R2 "The value is too large to fit in the column data area of the buffer." - Hi -- stuck on this problem. I have a package which takes in records like: [code="plain"] Type Data H ..... D ..... lots of data ... T...

SSIS Analysis Services Processing Task. - Hi SSIS - 2008R2 SSAS - 2012 I have got a very weird problem. I am trying to build a package on SSIS 2008...


Data Warehousing : Strategies and Ideas

How to store a central repository of data - I am fairly new to database design and haven't been able to find a lot on this question. I work for...


Database Design : Design Ideas and Questions

Cocktail Recipe Database Schema - Tables Question - Normalization - I'm fairly new to database design. I have done a few in the past, however, I am trying to normalize...


SQLServerCentral.com : Anything that is NOT about SQL!

What does a masters degree in database administration give you - Is there any justification for pursuing a masters degree in database administration? what would be the positives and the negatives?

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 : General

Can't figure this out?! - I've recently accepted a new DBA job. In fact I'm there first DBA. While trying to build a few queries...


Career : Certification

How to break into data warehouse management - Hi All, I'm hoping I can get some valuable input from people in the field. My current and past jobs have...


Career : Employers and Employees

Do you usually go to agencies? - Hi all database professionals. Want to ask you some questions about agencies and dealing with them. When you are looking for...

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 ©2014 Red Gate Software Ltd, Newnham House, Cambridge Business Park, Cambridge, CB4 0WZ, United Kingdom. All rights reserved.
Contact: webmaster@sqlservercentral.com