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

Stairway to SQL Server Agent: Level 1: Setup and Overview

Richard Waymire from SQLServerCentral.com

SQL Server Agent is a Microsoft Windows service that allows a DBA to automate administrative tasks. SQL Server Agent can run jobs, monitor SQL Server, and process alerts. The SQL Server Agent service must be running before any jobs scheduled to execute automatically can be run More »

Querying SQL Server Agent Job History Data

Additional Articles from MSSQLCity.com

Often times we are tasked with having to programmatically come with a list of durations per SQL Server Agent Job to trend the run times and order the results by date. Unfortunately it's not always easy in the way the data is stored in the system tables in the MSDB database. This tip explains how to use the system tables to get the data into the correct format for dates and job durations. More »

I've been hacked before. My personal web site has been hacked with a variety of injection and XSS attacks over the years. None too serious, and I've had backups that allowed me to fix things fairly easily, especially once I had a copy of Data Compare, which saved me a lot of time. At SQLServerCentral, we've been hacked as well, though not in a long time. I think we've closed most of the security holes, and I haven't had any issues to deal with in quite some time.

However as I was reading a note from Richard Douglas about being hacked, it brought back memories of working at JD Edwards. Richard was hacked at work, on his personal system. At JD Edwards, we were required to lock our workstations at all times when we were not physically in front of them. We also had two accounts: a normal user and a domain admin "privileged" user. As you might expect, there were numerous lapses of people walking to the kitchen or bathroom and forgetting to lock their workstations. It was considered fair game to change settings, send email to our group, even place semi-SFW pictures on someone's desktop. It was quite embarrassing to be caught, and was much more a an effective security reminder than a reprimand from our boss.

However there is a serious security problem here. Many of us would use our privileged account all too often, since it was a hassle to log out and back in. The "run as" option didn't work well for some applications, and we were less secure than we probably should have been. If someone walking by, whether an employee, guest, consultant, or someone else noticed SSMS running, how long would it take them to type:

  sp_addlogin 'joeuser', 'joeuser'
  sp_addrole 'joeuser', sysadmin

I type quickly and that took me less than 30 seconds. I'm sure even a slow typist could get that entered, and erased, inside of a minute. That might result in a serious security breech, if the system to which you were connected contained HIPAA, PCI, or any identity information. Perhaps even worse these days is the chance someone might attach a USB key logger to your keyboard.

You might be safe in your environment, but you can never be sure. A little care in ensuring you are not unnecessarily exposing security holes, and making sure that outsiders are always escorted can prevent embarrassing incidents from occurring.

Steve Jones from SQLServerCentral.com

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

• Watch the Windows Media Podcast - 29.4MB WMV
• Watch the iPod Video Podcast - 23.8MB MP4
• Listen to the MP3 Audio Podcast - 4.8MB MP3

The podcast feeds are available at sqlservercentral.mevio.com. Comments are definitely appreciated and wanted, and you can get feeds from there. Overall RSS Feed: or now on iTunes!

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.

You can also follow Steve Jones on Twitter:

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

Today's Question:

What will be returned by the following statements?

select ((CONVERT(NUMERIC(5,0),123) / CONVERT(NUMERIC(5,0),100)) * 100) AS [Percentage]
select ROUND(((CONVERT(Float,123) / CONVERT(Float,100)) * 100),1) AS [Percentag]

By Asif Khan

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

This question is worth 1 point in this category: T-SQL. 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.

SQL Server Hardware will provide the fundamental knowledge and resources you need to make intelligent decisions about choice, and optimal installation and configuration, of SQL Server hardware, operating system and the SQL Server RDBMS.

Pick up your copy of this great book from MVP Glenn Berry at Amazon today.

Which code below will return the date of New Year's Eve if run every day in January?

-- select 1
SELECT dateadd(day,-(datepart(day,dateadd(mm,1,GETDATE()))),dateadd(mm,-1,GETDATE())
-- select 2
SELECT dateadd(day,-(datepart(day,dateadd(mm,1,GETDATE())),GETDATE())
-- select 3
SELECT dateadd(day,-(datepart(day,dateadd(mm,1,GETDATE()))),dateadd(mm,1,GETDATE())

By Steve Jones

Answer: select 2

Explanation: The second SELECT statement will return '2012/12/31' for every day in January 2013.

» Discuss this question and answer on the forums

SQL Server 2012 Query Performance Tuning

SQL Server 2012 Query Performance Tuning leads you through understanding the causes of poor performance, how to identify them, and how to fix them. You’ll learn to be proactive in establishing performance baselines using tools like Performance Monitor and Extended Events. You’ll learn to recognize bottlenecks and defuse them before the phone rings. You’ll learn some quick solutions too, but emphasis is on designing for performance and getting it right, and upon heading off trouble before it occurs. Delight your users. Silence that ringing phone. Put the principles and lessons from SQL Server 2012 Query Performance Tuning into practice today.

Get your copy from Amazon today.

Find Largest size Tables in a Database

Hari Sharma from SQLServerCentral.com

To find a list of Tables (User or System tables)according to their size. More »

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

SQL Server 2005 : Administering

Question about server alias - We currently have two production servers at my company, let's call them DB1 and DB2, each with a handful of...

SQL Server 2005 : Backups

Restore database to a remote server - Hello Everyone, I am fairly new to this forum and have some experience with SQL. I was recently assigned a project...

SQL Server 2005 : Business Intelligence

SSRS - Calculate $ cost on monthly basis - I am new to SSRS and stuck at a point and can not move further and so need your help I...

SQL Server 2005 : SQL Server 2005 Performance Tuning

Turn off auto-stats at table level in 2005? - Hi Folks, Is it possible to turn off automatic stats gathering at the table-level in SQL 2005? I want to turn...

SQL Server 2005 : SQL Server 2005 Integration Services

Issue with script component when used as destination - Hi all, Need help in solving an issue. i have a SSIS pkg deployed in INT & Dev env respectively. The data flow...

SQL Server 2005 : T-SQL (SS2K5)

shrinking database - hi all, i want to know that ,can we shrink the database at the time of working on that database. if...

data for Min/Max rows - Hi All, Please can someone tell me how to select min/max rows in a single query similar to the one below. [code] Select...

SQL Server 2005 : SQL Server Newbies

Birthdays in next 7 days - Hi, I need to display all the employees whose date of birth comes in the next 7 days. I have tried...

SQL Server 7,2000 : Replication

SQL 2008 R2 Replication help - We have two separate production db servers whose only difference is data content. We are trying to publish the data...

SQL Server 2008 : SQL Server 2008 - General

SSRS--KeepTogether for Footers of Nested Groups - Running 2008 R2. I have reports with Row Groups nested to a level of 3 and 4. Each group has...

SQL Query including date - Hello I want to select records such that if a user logs in again within 10 minutes it should ignore...

SQL Server 2008 - profiler settings - I am collecting queries using sql profiler which are taking more than 10 seconds using column filters. I also want...

Upgrading to 2012? - Is it worth upgrading from SQL Server 2008 R2 Developer to 2012 Developer? I believe most companies are still using SQL...

How to install an oracle linked server on SQL Server 2008 r2 failover cluster manager - How to install an oracle linked server on SQL Server 2008 r2 failover cluster manager I'm reading some articles and applying...

SQL Server 2008 : T-SQL (SS2K8)

Turning raw data into a grid layout - Hi all, not sure how best to explain what I'm trying to do so will try to break it down as...

Remove duplicate records? - HI, How to find duplicate records as below query [code="sql"] Select A.SS_ID,A.SD_ID, A.SD_Info, A.SD_User, A.SD_Date from dbo.BC_ShiftSummaryInfo A Join dbo.BC_ShiftSummary B On A.SS_ID = B.SS_ID Where B.ShiftStartDate...

Query for delete records between date range? - Hi, Need Query for delete records between date range, I want delete --(3377 row(s) as below two tables [code="sql"]Select A.SS_ID,A.SD_ID, A.SD_Info, A.SD_User,...

is it possible to change an instance server properties with T-SQL ? - hi can i change the properties of a server instance with T-SQL ...like Enabling mixed mode or change the name of...

Need To Generate XML File - Hi, I Have Table Data For The Following Stucture.. [code="sql"]CREATE TABLE [dbo].[xmldata]( [FirstName] [varchar](110) NULL, [LastName] [varchar](210) NULL, [Email] [varchar](110) NULL, [Phone] [varchar](110) NULL, [Position] [varchar](110) NULL, [Branch]...

TSQL and percentile (not percentilerank :) - Hi all, I would like to ask for some help and maybe the best practices on calculating the 90th percentile using...

Time - Adding minutes and seconds - I have the 2 columns with time datatypes. select top 5 [Connect Time2],[totalTime2] from dbo.verizonDetails Connect Time2 totalTime2 08:05:44.0000000 00:13:00.0000000 08:05:57.0000000 00:01:00.0000000 09:07:42.0000000 00:03:00.0000000 09:07:46.0000000 00:09:00.0000000 09:08:08.0000000 00:01:00.0000000 I want to add the...

SQL Server 2008 : SQL Server Newbies

Taking a vertical list and transposing it to horizontal and then grouping - Hello, Is this possible? I have several columns, such as: Coulmn A red green yellow black Column B shirt jumper blouse coat Column C for women for men for girls extra large I have permutated the columns in...

Create Table and Bulk Insert - Hi guys, I have a .csv file wich has the following information (exactly like this): List Name: User Format Version:1.2.5.0 Date:12/11/2012 12:00:34...

SQL Server 2008 : Security (SS2K8)

Grant create proc permission to a user-defined role - First off, a Happy New Year 2013 to all. Maybe I'm suffering from the after-effects of last night but I...

editting data in an encrypted column - Hi all, I'm attempting (unsuccessfully) to reset a password, the issue is that the column with the passwords in is...

SQLServerCentral.com : Anything that is NOT about SQL!

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

How to do data quality ( name, address, account, data , matching/reconciliation) using SQL server 2008 - Dear Experts, Happy new year to all of you!!!! 1) Could you please provide some info on how to do data quality...

Reporting Services : Reporting Services

SSRS functionality - Hi all, I'm new to reporting services and i would like to see if the following objective can be accomplished...

Data Warehousing : Integration Services

SQL Server Integration and Browser services were stopped by its own - Hi all, In our environment Integration and Browser services were stopped by its own.I dono why its happening daily and manually...

Data Warehousing : Analysis Services

MDX Query - Anybody knows how to put in a table of sql server the result of a MDX Query? So, I write a...