July 19, 2012 at 3:05 pm
Hello, all!
Due to database corruption referenced in a previous post, I am attempting to transfer all objects and data out of one database into another using SSIS. So far, I have been able to troubleshoot many various errors, but I noticed something strange while trying to track this one down and it makes me wonder if I'm missing some setting or something. Here is the error...
Execution failed with the following error: "ERROR : errorCode=-1073548784 description=Executing the query "GRANT EXECUTE ON [DMUSER].[InsertHFR_WarrantyCard]..." failed with the following error: "Cannot find the object 'InsertHFR_WarrantyCard', because it does not exist or you do not have permission.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
I assumed it has to do with permissions of the DMUSER user and/or the DMUSER schema or something along those lines, but when I compare the source db to the new objects in the destination db, I find that the stored proc in the source is 'DMUSER.InsertHFR_WarrantyCard' and the one in the destination is now 'dbo.InsertHFR_WarrantyCard'. It appears that this didn't happen to all of the stored procs in the DMUSER schema, just certain ones. Why in the world would it do that??! :hehe:
~Any insite would be greatly appreciated!
(OT): If anyone just *happens* to read this post that *happens* to be attending SQL Saturday in Cleveland on Aug, 18 or the Ohio North SQL Server User Group on Aug, 7th...look me up! (I'm a brand-spankin-new'b!)
July 19, 2012 at 3:08 pm
It seems like the user in the old database had a default schema of DMUSER, but in the new (destination) the user has a default of dbo. Then, the SSIS task is creating the sp's in the context of the user maybe?
Jared
CE - Microsoft
July 23, 2012 at 6:41 am
Thanks, Jared...I think you're on to something there! When I run the task, it creates the user DMUSER in the destination db with a default schema of dbo, even though in the source db it has a default of DMUSER. Grrr, why does it do that?
Which results in some of the stored procs come over with DMUSER as the schema, some (that should have DMUSER) as dbo.
August 9, 2012 at 2:23 pm
Just a quick update. Prior to running the Object Task, I set up a T-SQL Task that would make the owner of the DMUSER schema dbo on the source db. I'm not quite sure if I understand why it worked, but I no longer get the error. 😉
April 18, 2013 at 7:23 am
Hi All,
I'm getting a similar error, "[Execute SQL Task] Error: Executing the query "Truncate Table Outcomes.ExtractTrackingTempTrunc..." failed with the following error: "Cannot find the object "ExtractTrackingTemp" because it does not exist or you do not have permissions.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly." when attempting to run an SSIS package that was working fine and deployed to production. I just need to make a change to a parameter, but am running into the above error. Why would it be doing this now, when I know it was fine in production? Any advice would be greatly appreciated!
Thanks.
April 18, 2013 at 7:40 am
Has the database been restored recently? Doing so could create orphans and it sounds like your permissions have changed. Ensure that the account on db you are truncating has sufficient rights/roles (db_owner, if necessary) and run auto_fix. That may help.
April 18, 2013 at 8:22 am
Hi,
I don't believe that the database was restored issue and when I considred the auto_fix, there are no orphaned users being shown. Any other suggestions would be greatly appreciated. Thanks!
April 18, 2013 at 9:17 am
Did you check the account that is running the command? In addition to the auto_fix, it could be a permissions/role issue:
IF NOT EXISTS(SELECT * FROM sys.database_principals WHERE name = N'xxx')
CREATE USER [xxx] FOR LOGIN [xxx]
EXEC sp_addrolemember db_owner, [xxx]
GO
The login and db user permissions may exist - but after a refresh, the db user may not come over if it didn't exist on the original db and you would need to create it or give the login account permissions to the db.
Even if it wasn't a refresh that caused it - it still sounds like the account that's running the truncate doesn't have sufficient rights to do so. Why the permissions changed, tho, you would have to determine. (e.g., Could the permissions have been manually changed?)
April 18, 2013 at 11:31 am
Thanks for your suggestion. It actually turns out that it was an issue with the configurations not pointing to the development server...all is well now!
Thanks again! 🙂
April 18, 2013 at 11:39 am
That'll do it! Glad you got it sorted out. 🙂
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply