question

clayp56 avatar image
clayp56 asked

SSIS Transfer database issue creating login

I am getting the following error message when I execute a transfer database task in SSIS. Message Executed as user: FLORILLI\\heather. Microsoft (R) SQL Server Execute Package Utility Version 10.50.1600.1 for 64-bit Copyright (C) Microsoft Corporation 2010. All rights reserved. Started: 2:20:50 PM Error: 2012-03-09 14:22:30.31 Code: 0xC0024104 Source: Transfer Database Task Description: The Execute method on the task returned error code 0x80131500 (ERROR : errorCode=-1073548784 description=Executing the query "CREATE USER [tom] FOR LOGIN [tom] WITH DEFAULT\_SCH..." failed with the following error: "'tom' is not a valid login 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. helpFile= helpContext=0 idofInterfaceWithError={C81DFC5A-3B22-4DA3-BD3B-10BF861A7F9C}). The Execute method must succeed, and indicate the result using an "out" parameter. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 2:20:50 PM Finished: 2:22:30 PM Elapsed: 100.075 seconds. The package execution failed. The step failed.
ssisdatabasetransfer
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Is it at all possible that the error occurs because the user named **heather** does not have permissions to create database users? If the login named **tom** is already a valid login then the lack of permission to create database user could be a problem. This one is easy enough to check though: - Login to the box as FLORILLI\\heather - Open SSMS and login to the server using windows authentication - Issue the statement to create user: use [YOUR_DB_NAME_HERE];
go create user [tom] for login [tom] with default_schema -- etc If the above barks with the same error (**'tom' is not a valid login or you do not have permission**) then the lack of permission is the issue.
1 Like 1 ·
Jay Bonk avatar image
Jay Bonk answered
It seems as though the package is trying to create the user tom in the database, and associate it with a login name tom as well. Does a login to the server exist?
3 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Yes, the login tom exists in the source database.
0 Likes 0 ·
The user exists in the database, but does the login exist on the server? If you look in the Security Folder -> Logins is there a login for tom there?
0 Likes 0 ·
Yes, he is there in the Security Folder on the source server / database.
0 Likes 0 ·
clayp56 avatar image
clayp56 answered
Looks like it was the permision problem. I tried to give her permissions and not sure if I did that correctly. Here is the message now when I execute the job: Executed as user: FLORILLI\heather. Microsoft (R) SQL Server Execute Package Utility Version 10.50.1600.1 for 64-bit Copyright (C) Microsoft Corporation 2010. All rights reserved. Started: 3:07:09 PM Error: 2012-03-15 15:07:29.57 Code: 0xC0024104 Source: Transfer Database Task Description: The Execute method on the task returned error code 0x80131500 (Drop failed for Database 'lme_1010_copy'. ). The Execute method must succeed, and indicate the result using an "out" parameter. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 3:07:09 PM Finished: 3:07:29 PM Elapsed: 20.545 seconds. The package execution failed. The step failed.
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.