Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SSIS Transfer SQL Server Object Task Expand / Collapse
Author
Message
Posted Thursday, July 19, 2012 3:05 PM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 12:28 PM
Points: 52, Visits: 446
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??!

~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!)
Post #1332588
Posted Thursday, July 19, 2012 3:08 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 11:23 AM
Points: 2,691, Visits: 3,376
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?

Thanks,

Jared
SQL Know-It-All

How to post data/code on a forum to get the best help - Jeff Moden
Post #1332593
Posted Monday, July 23, 2012 6:41 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 12:28 PM
Points: 52, Visits: 446
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.
Post #1333750
Posted Thursday, August 9, 2012 2:23 PM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 12:28 PM
Points: 52, Visits: 446
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.
Post #1343017
Posted Thursday, April 18, 2013 7:23 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Yesterday @ 2:20 PM
Points: 38, Visits: 169
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.
Post #1443837
Posted Thursday, April 18, 2013 7:40 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 12:28 PM
Points: 52, Visits: 446
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.


Post #1443865
Posted Thursday, April 18, 2013 8:22 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Yesterday @ 2:20 PM
Points: 38, Visits: 169
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!
Post #1443899
Posted Thursday, April 18, 2013 9:17 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 12:28 PM
Points: 52, Visits: 446
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?)
Post #1443938
Posted Thursday, April 18, 2013 11:31 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Yesterday @ 2:20 PM
Points: 38, Visits: 169
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!
Post #1444044
Posted Thursday, April 18, 2013 11:39 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 12:28 PM
Points: 52, Visits: 446
That'll do it! Glad you got it sorted out. :)
Post #1444049
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse