This post discusses about configuring different user account to JOB running in SQL Server 2008. By default SQL JOBs runs under service account that you have configured while installing the SQL Server. You can follow the below steps to change the user account that running under JOB

1. Open SQL Server Management Studio and expand the SQL Server Agent node and Select the JOB that you want to change the running account.



2. Double click the selected JOB then you will get the below dialogue



3. Select the Steps from the above window and click Edit to see the below properties window



4. Click the ellipses button right to Run as user and select the user from below window and say ok



Now your job will run under chosen user account. It is always recommended to run your SQL JOBS under one account rather putting to many user accounts for to many JOBS. This post is for the people who have the business reason and want to run under different user account.

Error message when I try to restore a database backup to SQL Server 2008



Msg 3176, Level 16, State 1, Line 1 File 'D:\SQL Server 2008 DBs\test01.mdf' is claimed by 'SCTA_ORG_SECOND'(3) and 'SCTA_ORGANIZATION'(1). The WITH MOVE clause can be used to relocate one or more files.

Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally

Steps to fix this issue:

select Script Action to New Query Window as the above image so you will get the following T-SQL and here we will know the reason

RESTORE DATABASE [test01] FROM  DISK = N'D:\SCTA_Org2.bak' WITH  FILE = 1,
    MOVE N'SCTA_ORGANIZATION' TO N'D:\SQL Server 2008 DBs\test01.mdf',
    MOVE N'SCTA_ORG_SECOND' TO N'D:\SQL Server 2008 DBs\test01.MDF',
    MOVE N'SCTA_ORGANIZATION_log' TO N'D:\SQL Server 2008 DBs\test01_1.ldf',
    NOUNLOAD,  STATS = 10
GO

so you will notice there are two files of mdf with the same name so just change the name of second one to test02 or to test01.ndf ( different extension) then run the command and it's successfully restored.

so the logical answer for this error first test01.mdf is a primary data file and the second is the secondary data file but the extension and name are same so that way you have to change the name or extension of second file with any other name or extension.

Note: the extension is anything ( it can be .fad or .ndf but .ndf is best practice to determine what this file for for example .ldf for log file , .ndf  for secondary data files ..).

Finally : I think the original database backup come from SQL Server 2000 and maybe this behavior allowed in SQL Server 2000 or the name is a case sensitive ( test01.mdf not like test01.MDF).