x

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.
more ▼

asked Mar 09 '12 at 09:30 PM in Default

clayp56 gravatar image

clayp56
71 9 9 9

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.
Mar 14 '12 at 09:29 PM Oleg
(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first
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?
more ▼

answered Mar 10 '12 at 05:26 PM

Jay Bonk gravatar image

Jay Bonk
1.4k 2

Yes, the login tom exists in the source database.
Mar 14 '12 at 02:05 PM clayp56

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?

Mar 14 '12 at 02:38 PM Jay Bonk
Yes, he is there in the Security Folder on the source server / database.
Mar 14 '12 at 08:47 PM clayp56
(comments are locked)
10|1200 characters needed characters left

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.

more ▼

answered Mar 15 '12 at 08:18 PM

clayp56 gravatar image

clayp56
71 9 9 9

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x900
x235
x4

asked: Mar 09 '12 at 09:30 PM

Seen: 2083 times

Last Updated: Mar 15 '12 at 08:18 PM