March 11, 2008 at 9:30 am
Hello,
Below were the steps I was told to run, I ran everything the way I beleive suppose to,
1.Create database OPSAP on prod SQL (ISLRPTSQL)
2. Run SQL script from fail in attachment to create tables
3. Create database user “CnSUser” the same as it in OPSAP db on REDBOXC98
4. Copy all data to tables in new OPSAP database as follow
from OPSAP database on REDBOXC98 -
T_ActionTKN
T_ActionTime
T_BRANCH
T_BranchLink
T_BranchRegion
T_ImdAction
T_Mail
T_RootCause
T_TVC2_Reason
T_TVKK_Type
T_TopCustomer
T_USER
T_UsrPassword
from RAudit_DB database on ISLRPTSQL
T_IssResponse
5. Copy triggers from OPSAP database on REDBOXC98 for tables T_VBKA_Sales and T_VBKA_ALLSales respectively
6. Copy folder OPSAP1 on ISLWEB server and save it somewhere as back up.
7. Copy OPSAP1 website from ISLINTRANET web server to ISLWEB.
8. Change SQL server name in configuration file of OPSAP1 website.
9. In DTS package OPSAP_GetVBKA on ISLRPTSQL comment in script task line(in green) and add new line (in red) as below
sConnStringSQL = "Provider=MSDASQL;Driver={SQL Server};SERVER=ISLRPTSQL;DATABASE=RAudit_DB;uid=CnSUser;pwd=CnSUser;"
10. sConnStringSQL = "Provider=MSDASQL;Driver={SQL Server};SERVER=ISLRPTSQL;DATABASE=OPSAP;uid=CnSUser;pwd=CnSUser;"
also in this package set value of global variable dateFrom as 20080101
11. In DTS package OPSAP_SETtext on ISLRPTSQL comment in script task line(in green) and add new line (in red) as below
sConnStringSQL = "Provider=MSDASQL;Driver={SQL Server};SERVER=ISLRPTSQL;DATABASE=RAudit_DB;uid=CnSUser;pwd=CnSUser;"
12. sConnStringSQL = "Provider=MSDASQL;Driver={SQL Server};SERVER=ISLRPTSQL;DATABASE=OPSAP;uid=CnSUser;pwd=CnSUser;"
13. Change run time period for job OPSAP_GetVBKA on ISLRPTSQL from 35 minutes to 25 minutes.
Check out that mail agent is operational on ISLRPTSQL server.
After I do this I ran the job I get the below message,
Executed as user: ICS\sqlsrvr. ...n OnStart: DTSStep_DTSActiveScriptTask_1 DTSRun OnError: DTSStep_DTSActiveScriptTask_1, Error = -2147220482 (800403FE) Error string: Error Code: 0 Error Source= Microsoft OLE DB Provider for ODBC Drivers Error Description: [Microsoft][ODBC SQL Server Driver][SQL Server]User does not have permission to perform this operation on table 'T_VBKA_Sales'. Error on Line 107 Error source: Microsoft Data Transformation Services (DTS) Package Help file: sqldts80.hlp Help context: 4500 Error Detail Records: Error: -2147220482 (800403FE); Provider Error: 0 (0) Error string: Error Code: 0 Error Source= Microsoft OLE DB Provider for ODBC Drivers Error Description: [Microsoft][ODBC SQL Server Driver][SQL Server]User does not have permission to perform this operation on table 'T_VBKA_Sales'. Error on Line 107 Error source: Microsoft Data Transformation Services (DTS) Packag... Process Exit Code 1. The step failed.
I would highly appreciate if I can be assissted to this,
Thnak,
Devinder Sohal
March 11, 2008 at 9:36 am
User does not have permission to perform this operation on table 'T_VBKA_Sales'.
Who is the owner of the job you created? I don't know what you normally do to setup jobs, but most use 'sa' as the job owner (which would use the service account's permissions) to run the job. Basically the current owner of the job does not have access to read from your table that the DTS package is talking to. Either change the job owner to a user who has privileges, or give privileges to the job owner on that table.
Also, just tossing this out there (unless you already masked them?), you shouldn't have your passwords be the same as your usernames.
March 11, 2008 at 10:18 am
Hello,
I have the same user as the owner of the job, which is ICS\sqlsrvr,
Please Advice,
Thanks,
Devinder Sohal
March 11, 2008 at 10:21 am
Adam has hit it on the head. It's a security issue.
Check the rights on the Sales table and then who is running the package?
March 11, 2008 at 10:26 am
job owner is ics\sqlsrvr, package is run by ics\rluu, can I change the name in package
Please advice,
thanks,
Dev
March 11, 2008 at 10:30 am
dsohal (3/11/2008)
job owner is ics\sqlsrvr, package is run by ics\rluu, can I change the name in packagePlease advice,
thanks,
Dev
What rights does ics\sqlsrvr have? Is that the account running your SQL Server service? Is he a local admin? Do builtin admins have sysadmin privileges?
March 11, 2008 at 10:37 am
Yes ics\sqlsrvr is the SQL Sever Service account, it is account which is local admin too, and I have all the tasks with this account
March 11, 2008 at 10:42 am
This DTS is all focused on this one SQL server yes? Are you connecting to another server at any point? Are you using liked servers or opendatasource at all?
If that account is in fact a sysadmin, and is the account which is running the job ... I'm not sure what to tell you as that does not add up. Have you checked the permissions of the table in question to ensure that there are no deny's? Can you run the package with your credentials (assuming you have the rights to do so)?
March 11, 2008 at 1:29 pm
Check to see if ics\rluu (i.e. who runs the package) has the necessary permissions on the table.
March 11, 2008 at 1:40 pm
hello,
actually I am sending the script here, if I am not wrong isn't the uesr ics\cnsuser here, and should be providing enough permission to this account, please advice,
'// sConnStringSQL = "Provider=MSDASQL;Driver={SQL Server};SERVER=(local);DATABASE=RAudit_DB;uid=RTA_User;pwd=RTA_User;"'
'// sConnStringSQL = "Provider=MSDASQL;Driver={SQL Server};SERVER=ISLDEVSQL;DATABASE=RAudit_DB;uid=CnSUser;pwd=CnSUser;"
sConnStringSQL = "Provider=MSDASQL;Driver={SQL Server};SERVER=ISLRPTSQL;DATABASE=RAudit_DB;uid=CnSUser;pwd=CnSUser;"
'// sConnStringSQL = "Provider=MSDASQL;Driver={SQL Server};SERVER=ISLRPTSQL;DATABASE=OPSAP;uid=CnSUser;pwd=CnSUser;"
'// sConnStringSQL = "Provider=MSDASQL;Driver={SQL Server};SERVER=ISLRPTSQL;DATABASE=RAudit_DB;uid=ics\svc-cnsuser;pwd=cnsxuser;"
Thanks,
Dev
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply